Can someone help me make this dynamic?

[Henry Stock]

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
(e-mail address removed)

 

[GaryDK]

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

 

[Henry Stock]

Doesn't that code make assumptions about the Range name?