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

 

[Wolf]

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

Wolf

 

[skuzapo]

Thanks for your help Wolf and Rowan,

Very helpful posts thank