Automatic nameing of ranges based on a label

[skuzapo]

I have a sheet in which I need to name a whole lot of cells. I'm getting rsi trying to do them manually and am now trying to write some code to name the ranges based on a label which is in the column to the right of the cell to be named. I need to name about 50 cells going down....

I have tried my code on naming one cell first and planning to move on to the moving down to the next cell once this is working....

However, this doesn't work!


Dim Nme As String
Dim Pos
Dim MySheet As String

Nme = ActiveCell.Offset(0, 1)
Pos = ActiveCell.Address

ActiveWorkbook.Names.Add Nme, Pos
End Sub


Please help.
Thanks in advance

[Rowan]

Assuming your list of names is in column A and you want the named cells
to be in column B then:

Sub naming()
Dim Nm As String
Dim eRow As Long
Dim i As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To eRow
Nm = Cells(i, 1).Value
ActiveWorkbook.Names.Add Name:=Nm, RefersTo:=Cells(i, 2)
Next i
End Sub

Hope this helps
Rowan

skuzapo wrote:
> I have a sheet in which I need to name a whole lot of cells. I'm getting
> rsi trying to do them manually and am now trying to write some code to
> name the ranges based on a label which is in the column to the right of
> the cell to be named. I need to name about 50 cells going down....
>
> I have tried my code on naming one cell first and planning to move on
> to the moving down to the next cell once this is working....
>
> However, this doesn't work!
>
>
> Dim Nme As String
> Dim Pos
> Dim MySheet As String
>
> Nme = ActiveCell.Offset(0, 1)
> Pos = ActiveCell.Address
>
> ActiveWorkbook.Names.Add Nme, Pos
> End Sub
>
>
> Please help.
> Thanks in advance
>
>

[Wolf]

activecell.name = activecell.offset(0,1).value

Wolf

"skuzapo" wrote:

>
> I have a sheet in which I need to name a whole lot of cells. I'm getting
> rsi trying to do them manually and am now trying to write some code to
> name the ranges based on a label which is in the column to the right of
> the cell to be named. I need to name about 50 cells going down....
>
> I have tried my code on naming one cell first and planning to move on
> to the moving down to the next cell once this is working....
>
> However, this doesn't work!
>
>
> Dim Nme As String
> Dim Pos
> Dim MySheet As String
>
> Nme = ActiveCell.Offset(0, 1)
> Pos = ActiveCell.Address
>
> ActiveWorkbook.Names.Add Nme, Pos
> End Sub
>
>
> Please help.
> Thanks in advance
>
>
> --
> skuzapo
> ------------------------------------------------------------------------
> skuzapo's Profile: http://www.excelforum.com/member.php...o&userid=27430
> View this thread: http://www.excelforum.com/showthread...hreadid=470655
>
>

[skuzapo]

Thanks for your help Wolf and Rowan,

Very helpful posts thanks