| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How reference cell in different worksheet
Im 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, Ive tried using a variable but that didn't work either Worksheets("1").Cells(C.Address, 2) = "Row" & FromRow Any suggestions? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How reference cell in different worksheet
|
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How reference cell in different worksheet
I don't understand, but maybe...
Worksheets("1").Cells(C.Row, 2) = "Row" & FromRow But I have no idea what FromRow is... laavista wrote: Im 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, Ive tried using a variable but that didn't work either Worksheets("1").Cells(C.Address, 2) = "Row" & FromRow Any suggestions? -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How reference cell in different worksheet
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 Microsoft MVP Excel SalesAid Software "laavista" wrote in message ... 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" wrote: Methinks you left something out. -- Don Guillett Microsoft MVP Excel SalesAid Software "laavista" wrote in message ... Im 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, Ive tried using a variable but that didn't work either Worksheets("1").Cells(C.Address, 2) = "Row" & FromRow Any suggestions? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How reference cell in different worksheet
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 "laavista" wrote in message ... 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" wrote: Methinks you left something out. -- Don Guillett Microsoft MVP Excel SalesAid Software "laavista" wrote in message ... Im 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, Ive tried using a variable but that didn't work either Worksheets("1").Cells(C.Address, 2) = "Row" & FromRow Any suggestions? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How reference cell in different worksheet
This was the answer! Thank you so much!!!!
"Don Guillett" wrote: 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 "laavista" wrote in message ... 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" wrote: Methinks you left something out. -- Don Guillett Microsoft MVP Excel SalesAid Software "laavista" wrote in message ... Im 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, Ive tried using a variable but that didn't work either Worksheets("1").Cells(C.Address, 2) = "Row" & FromRow Any suggestions? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How reference cell in different worksheet
Glad to help
-- Don Guillett Microsoft MVP Excel SalesAid Software "laavista" wrote in message ... This was the answer! Thank you so much!!!! "Don Guillett" wrote: 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 "laavista" wrote in message ... 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" wrote: Methinks you left something out. -- Don Guillett Microsoft MVP Excel SalesAid Software "laavista" wrote in message ... Im 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, Ive tried using a variable but that didn't work either Worksheets("1").Cells(C.Address, 2) = "Row" & FromRow Any suggestions? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| WORKSHEET NAME = CELL REFERENCE | Excel Worksheet Functions | |||
| WORKSHEET NAME = CELL REFERENCE | Excel Worksheet Functions | |||
| Reference another worksheet using a cell | Excel Discussion (Misc queries) | |||
| Worksheet reference (i.e placing worksheet name in a cell) | Excel Worksheet Functions | |||
| Reference a cell to get worksheet name | Excel Worksheet Functions | |||
Linear Mode
