Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row select mode to highlight active row of active cell | Excel Discussion (Misc queries) | |||
referring to formula in a non active cell from active cell | Excel Discussion (Misc queries) | |||
Macro to paste in the active cell the contents of a cell from another file?? | Excel Programming | |||
I need to sort an active sheet using the col of the active cell | Excel Programming | |||
Copy from active sheet and paste into new sheet using info from cell in active | Excel Programming |