Name an active cell
 

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!

--
Steve C

Name an active cell
 

Steve,
Recorded macro's are noterious for hard coding references. I would
suggest you do something like this. It will transfer between workbooks with
ease. I would not suggest you use it in the same workbook on a different
sheet, since named ranges can be sheet specific and would require further
coding.

Sub FindFinal()
Dim wb As Workbook, ws As Worksheet, Found As Range

Set wb = ActiveWorkbook
Set ws = ActiveSheet
On Error GoTo ErrHandler

Set Found = ws.Cells.Find("Final")

wb.Names.Add "Location", Found

Exit Sub
ErrHandler:
Select Case Err.Number

Case Else
MsgBox Err.Number & " " & Err.Description
End Select
End Sub

HTH
Cal



"Steve C" wrote:

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!

--
Steve C