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
[email protected]

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

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

"GaryDK" <[email protected]> wrote in message
news:[email protected]...
> 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
>