Cell naming using cell references (VBA? Offset?)

[StephenT]

Hello,

I have a table. I need to assign a name to each of the cells but it is
extremely tedious even using the name manager. (I also have to do it multiple
times). I have been trying to create the code in VBA but am making a
ham-fisted frustration of the job. I know this involves offsets and an
iteration but I'm afraid my skills do not extend to this. Can you help?
Here's what I want to do:

a b c ... n
1 colnameb colnamec... colnamen
2 rowname2
3 rowname3
...
M rownamem


I want the cell in b2 to be named rowname2colnameb
....the cell in c2 to be named rowname2colnamec
....the cell in NM to be named rownamemcolnamen

The tables are in different positions, so please presume I am starting the
macro with the cursor in cell b2.

Thankyou in advance!!!

 

[Jim Thomlinson]

Give this a whirl. Run DoStuff. It Passes in the Table range that you wnat to
add the names to...

Sub DoStuff() 'Run me***
Call CreateNames(Range("A14"))
End Sub

Sub CreateNames(ByVal Target As Range)
Dim rng As Range
Dim rngToTraverse As Range

With Target
Set rngToTraverse = .Parent.Range(.Cells(1).Offset(1, 1), .Cells(.Count))
End With

For Each rng In rngToTraverse
Application.Names.Add Cells(Target(1).Row, rng.Column).Value & _
Cells(rng.Row, Target(1).Column).Value, rng
Next rng
End Sub

 

[Joel]

Just modify the 1st two lines as required

StartRow = 1
StartLetter = "A"



Sub Makelinks()

StartRow = 1
StartLetter = "A"

StartCol = Range(StartLetter & "1").Column
Set StartCell = Range(StartLetter & StartRow)

LastRow = StartCell.Offset(1, 0).End(xlDown).Row
LastCol = StartCell.Offset(0, 1).End(xlToRight).Column

'name first column
For RowCount = (StartRow + 1) To LastRow
CellName = "rowname" & RowCount
ActiveWorkbook.Names.Add _
Name:=CellName, _
RefersToR1C1:="=" & _
Cells(RowCount, StartCol).Address( _
ReferenceStyle:=xlR1C1, external:=True)
Next RowCount

'name first Row
For ColCount = (StartCol + 1) To LastCol
ColAddr = Cells(StartRow, ColCount).Address( _
ReferenceStyle:=xlA1, external:=False)
'remove 1st dollar sign
ColAddr = Mid(ColAddr, 2)
'remove letter from rest of string
ColLetter = Left(ColAddr, InStr(ColAddr, "$") - 1)
CellName = "colname" & ColLetter
ActiveWorkbook.Names.Add _
Name:=CellName, _
RefersToR1C1:="=" & _
Cells(StartRow, ColCount).Address(ReferenceStyle:=xlR1C1,
external:=True)
Next ColCount

'name table cells
For RowCount = (StartRow + 1) To LastRow
For ColCount = (StartCol + 1) To LastRow
ColAddr = Cells(RowCount, ColCount).Address( _
ReferenceStyle:=xlA1, external:=False)
'remove 1st dollar sign
ColAddr = Mid(ColAddr, 2)
'remove letter from rest of string
ColLetter = Left(ColAddr, InStr(ColAddr, "$") - 1)

CellName = "rowname" & RowCount & "colname" & ColLetter

ActiveWorkbook.Names.Add _
Name:=CellName, _
RefersToR1C1:="=" & _
Cells(RowCount, ColCount).Address( _
ReferenceStyle:=xlR1C1, external:=True)
Next ColCount
Next RowCount

End Sub