Adding numerous named ranges

[Mcaa51]

Does anyone know what is wrong with the below? I am trying to use the value in the upper left corner of various ranges as the range's name.

Sub SetRange()
Dim Counter As Integer
Dim LastRow As Integer
Dim Rng As Range
Dim RngName As String

For Counter = 254 To 6 Step -2
LastRow = ActiveSheet.Cells(65536, Counter).End(xlUp).Row
ThisWorkbook.Names.Add Name:=ActiveSheet.Cells(2, Counter -1).Value, _
RefersToR1C1:=Range(Cells(2, Counter - 1), Cells(LastRow, Counter))

Next Counter
End Sub

[Bob Phillips]

Can you give an example of say two ranges it should create names for, and where they lie, their cell adresses?

[Mcaa51]

Well, they lie in various places. The RefersToR1C1 portion of the below appears to be working correctly. What I am having trouble with is that I want to use values from the worksheet as the name for the ranges. I tried using the statement

ThisWorkbook.Names.Add Name:=ActiveSheet.Cells(2, Counter -1).Value

But that doesn't seem to be working. I also tried

RngName = ActiveSheet.Cells(2, Counter -1).Value
ThisWorkbook.Names.Add Name:= RngName

but that doesn't appear to work either. I guess my question is how do you set a range name using a variable so that I don't have to explicitly name each range, but instead can pull the name from the top left cell in the range?

[SamT]

I use this code, where Column_Converter.ColumnCharacters() returns the column's alpha designators.

I see that I use the Cell's Text property and feed a String to RefersTo.

Do While ThisCell.Text <> ""
ThisColumn = Column_Converter.ColumnCharacters(ThisCell.Column)
Reference_String = "$" & ThisColumn _
& ":" & ThisColumn & "65536"
Names.Add Name:=SheetName & "!" & ThisCell.Text, _
RefersTo:=Reference_String

Set ThisCell = ThisCell.Offset(0, 1)
Loop


I'm using it to walk across a Row and create sheet qualified names based on the Cell's contents

[Bob Phillips]

<snip>

I guess my question is how do you set a range name using a variable so that I don't have to explicitly name each range, but instead can pull the name from the top left cell in the range?



rng.Name = rng.Cells(1,1).Value2