Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me make this dynamic?
I am trying to give a set of fields range names so that I can use those
names elsewhere in my macros. In all cases the cell's caption is located in the cell directly left of the one I want to name. I search for the caption. I select the cell to its right I attempt to name that cell by the range name. Here is the problem code: RefersToR1C1:= "='IA Control Documentation'!R9C2" The problem is that the ReferToR1C1 clause is sticking in what appears to be a hardcoded reference to a specific cell on a specific sheet. Yet when I try to refer ActiveCell.Address it tells me I need a collection. I don't know how to revise that to reference the cell as a collection. ========================================= 'Locate the field that contains the main process name Range("A1").Select Cells.Find(What:="Process:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 1).Select ' Give this a range name ActiveWorkbook.Names.Add Name:="ProcessName", RefersToR1C1:= _ "='IA Control Documentation'!R9C2" =========================================== -- Henry Stock, onProject.com 3 Wing Drive Cedar Knolls, NJ 07927-1006 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me make this dynamic?
Hi Henry,
Try replacing the following lines: ActiveCell.Offset(0, 1).Select ' Give this a range name ActiveWorkbook.Names.Add Name:="ProcessName", RefersToR1C1:= _ "='IA Control Documentation'!R9C2" with these lines: ' also select the cell to the right ActiveCell.Resize(1, 2).Select ' give this cell the range name Selection.CreateNames Left:=True Gary |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I make a vLookup dynamic? | Excel Worksheet Functions | |||
how to make a dynamic workbook? | Excel Discussion (Misc queries) | |||
How to make worksheet names dynamic? | Excel Worksheet Functions | |||
Make dynamic charts more dynamic | Charts and Charting in Excel | |||
PivotChart: how to make it dynamic? | Charts and Charting in Excel |