How reference cell in different worksheet

  • Thread starter Thread starter laavista
  • Start date Start date
L

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?
 
I don't understand, but maybe...

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

But I have no idea what FromRow is...
 
I don't understand, but maybe...

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

But I have no idea what FromRow is...
 
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?
 
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?
 
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.
 
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.
 
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
 
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
 
This was the answer! Thank you so much!!!!



Don Guillett said:
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)
Click to expand...
 
This was the answer! Thank you so much!!!!



Don Guillett said:
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)
Click to expand...
 
Back
Top