How reference cell in different worksheet

[laavista]

I’m comparing values in worksheet 2 against values in worksheet 1. Once
found, I put the cell,column info in the 2nd worksheet indicating where it
was found. This works.

I also want to put cell,column in the 1st worksheet, though, indicating
where it is located in the 1st worksheet. This does not work.

The procedure is in the 2nd worksheet:

Set C = .Find(ReqNum, LookIn:=xlValues)

If Not C Is Nothing Then
FoundAddress = C.Address ' get row & column where reqnum
is found
' the following works
Worksheets("2").Cells(WriteToRow, 2) = "Row" & FoundAddress

' the following does NOT work
Worksheets("1").Cells(C.Address, 2) = "Row" & FromRow
End If

Instead of c.address below, I’ve tried using a variable but that didn't work
either Worksheets("1").Cells(C.Address, 2) = "Row" & FromRow

Any suggestions?

 

[Don Guillett]

Methinks you left something out.

 

[Don Guillett]

Methinks you left something out.

 

[Dave Peterson]

I don't understand, but maybe...

Worksheets("1").Cells(C.Row, 2) = "Row" & FromRow

But I have no idea what FromRow is...

 

[Dave Peterson]

I don't understand, but maybe...

Worksheets("1").Cells(C.Row, 2) = "Row" & FromRow

But I have no idea what FromRow is...

 

[laavista]

Let me rephrase my question...

I'm using the find method in worksheet2 to find a string in worksheet1.
I find the string in worksheet1 and want to know how to reference the row it
was found on. I want this info as I'm putting text in the same row, next
column where the info was found.

Make sense?

 

[laavista]

Let me rephrase my question...

I'm using the find method in worksheet2 to find a string in worksheet1.
I find the string in worksheet1 and want to know how to reference the row it
was found on. I want this info as I'm putting text in the same row, next
column where the info was found.

Make sense?

 

[Don Guillett]

NO. Let me ask you again to post all of your code along with which sheet is
the source and which is the dest and before/after examples. Or, send your
file to my address with same.

 

[Don Guillett]

NO. Let me ask you again to post all of your code along with which sheet is
the source and which is the dest and before/after examples. Or, send your
file to my address with same.

 

[Don Guillett]

I am still wondering what you want but will this do? Change sheet names,
ranges to suit.

Sub findemSAS()
For Each mc In Sheets("source").Range("b1:b3")
Set c = Sheets("dest").Range("a1:a21").Find(mc)
If Not c Is Nothing Then
foundrow = c.Row
Sheets("source").Cells(mc, "e") = c.Address
Sheets("dest").Cells(mc.Row, "e") = c.Address
End If
Next mc
End Sub

 

[Don Guillett]

I am still wondering what you want but will this do? Change sheet names,
ranges to suit.

Sub findemSAS()
For Each mc In Sheets("source").Range("b1:b3")
Set c = Sheets("dest").Range("a1:a21").Find(mc)
If Not c Is Nothing Then
foundrow = c.Row
Sheets("source").Cells(mc, "e") = c.Address
Sheets("dest").Cells(mc.Row, "e") = c.Address
End If
Next mc
End Sub

 

[laavista]

This was the answer! Thank you so much!!!!

I am still wondering what you want but will this do? Change sheet names,
ranges to suit.

Sub findemSAS()
For Each mc In Sheets("source").Range("b1:b3")
Set c = Sheets("dest").Range("a1:a21").Find(mc)
If Not c Is Nothing Then
foundrow = c.Row
Sheets("source").Cells(mc, "e") = c.Address
Sheets("dest").Cells(mc.Row, "e") = c.Address
End If
Next mc
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[laavista]

This was the answer! Thank you so much!!!!

I am still wondering what you want but will this do? Change sheet names,
ranges to suit.

Sub findemSAS()
For Each mc In Sheets("source").Range("b1:b3")
Set c = Sheets("dest").Range("a1:a21").Find(mc)
If Not c Is Nothing Then
foundrow = c.Row
Sheets("source").Cells(mc, "e") = c.Address
Sheets("dest").Cells(mc.Row, "e") = c.Address
End If
Next mc
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

Glad to help

 

[Don Guillett]

Glad to help