S
Steve C
How do I name an active cell without creating an absolute reference to that
cell if I run the same procedure in a different workbook?
For example, I'm trying to edit a macro I recorded that found the text
"Final" in cell A10 of the active worksheet, and then named that cell
"Location". If I run this macro on a different workbook, where "Final" is
located in B5, it still names cell A10 as "Location", rather than the desired
B5. No surprise there, but I'm struggling with changing the recorded code
not to refer to A10. Below is the recorded code:
Cells.Find(What:="Final", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveWorkbook.Names.Add Name:="Location", RefersToR1C1:="=Sheet1!R10C1"
I tried creating a variable = active cell address, then substituting it into
the code as follows (to no avail):
Cells.Find(What:="Final", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Test = ActiveCell.Address
ActiveWorkbook.Names.Add Name:="Location", RefersToR1C1:="=Sheet1!" +
Range("Test")
I also tried:
ActiveWorkbook.Names.Add Name:="Location", RefersToR1C1:="=Sheet1!" +Test
ActiveWorkbook.Names.Add Name:="Location", RefersToR1C1:="=Sheet1!" +Test"
ActiveWorkbook.Names.Add Name:="Location", RefersToR1C1:="=Sheet1!Test"
ActiveWorkbook.Names.Add Name:="Location", RefersToR1C1:="=Sheet1!" + "Test"
Help me get over the hump! Thanks!
cell if I run the same procedure in a different workbook?
For example, I'm trying to edit a macro I recorded that found the text
"Final" in cell A10 of the active worksheet, and then named that cell
"Location". If I run this macro on a different workbook, where "Final" is
located in B5, it still names cell A10 as "Location", rather than the desired
B5. No surprise there, but I'm struggling with changing the recorded code
not to refer to A10. Below is the recorded code:
Cells.Find(What:="Final", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveWorkbook.Names.Add Name:="Location", RefersToR1C1:="=Sheet1!R10C1"
I tried creating a variable = active cell address, then substituting it into
the code as follows (to no avail):
Cells.Find(What:="Final", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Test = ActiveCell.Address
ActiveWorkbook.Names.Add Name:="Location", RefersToR1C1:="=Sheet1!" +
Range("Test")
I also tried:
ActiveWorkbook.Names.Add Name:="Location", RefersToR1C1:="=Sheet1!" +Test
ActiveWorkbook.Names.Add Name:="Location", RefersToR1C1:="=Sheet1!" +Test"
ActiveWorkbook.Names.Add Name:="Location", RefersToR1C1:="=Sheet1!Test"
ActiveWorkbook.Names.Add Name:="Location", RefersToR1C1:="=Sheet1!" + "Test"
Help me get over the hump! Thanks!