Naming ranges of cells using cell content

[zhileezy]

Hi all,

I am trying to write a macro that would name sets of cells, from column C to column BM, with the first cell in column C (top left of the range) equals 60 and last cell in column C (bottom left of the range ) equals 600. The name of each range of cells would be the cell content on column A & column B. For example, if I want to highlight C7:BM60, and A7 = "101" and B7 = "1991", I want to name the range "101-1991". Here is my macro, and I'd really appreciate it if someone can tell me why the last line doesn't work. Thanks!!!

Sub Macro1()

Dim i As Double
Dim i2 As Double
Dim sectionname As String

i = 2
i2 = 2

For i = 2 To 4744

i = i2
Do
i = i + 1
Loop Until Worksheets("CF10").Range("c" & i) = 60

i2 = i
Do
i2 = i2 + 1
Loop Until Worksheets("CF10").Range("c" & i2) = 600

sectionname = Range("a" & i).Text & "-" & Range("b" & i).Text
ActiveWorkbook.Names.Add name:=sectionname, RefersTo:="=C" & i & ":BM" & i2, Visible:=True

Next i
End Sub

[georgiboy]

Welcome to the forum

I dont think you can use the "-" sign in a range name try the "_" instead

Is this what you are trying to achieve...

[vba]Sub Macro1()
Dim Firstrow As Long
Dim EndRow As Long
Dim rCell As Range
Dim EndData As Long
Dim SectionName As String

With Worksheets("CF10")
EndData = .Range("C" & Rows.Count).End(xlUp).Row

For Each rCell In .Range("C2:C" & EndData).Cells
Select Case rCell.Value
Case 60: Firstrow = rCell.Row
Case 600: EndRow = rCell.Row
End Select
Next rCell
SectionName = .Range("A" & Firstrow).Value & "_" & .Range("B" & Firstrow).Value
End With

ActiveWorkbook.Names.Add Name:=SectionName, RefersTo:=Range("C" & Firstrow & ":BM" & EndRow), Visible:=True

End Sub[/vba]

Hope this helps

[zhileezy]

Hi Georgiboy, thanks a lot for your help. However, both my code and yours incur a run-time error '1004' at the bottom line. I played around with it and it seems that I can only name the ranges with constants (a fixed string value) rather than the variable SectionName. Any idea how to fix that?

Thanks!!!

[georgiboy]

If you use this...

[VBA]Sub Macro1()
Dim Firstrow As Long
Dim EndRow As Long
Dim rCell As Range
Dim EndData As Long
Dim SectionName As String

With Worksheets("CF10")
EndData = .Range("C" & Rows.Count).End(xlUp).Row

For Each rCell In .Range("C2:C" & EndData).Cells
Select Case rCell.Value
Case 60: Firstrow = rCell.Row
Case 600: EndRow = rCell.Row
End Select
Next rCell
SectionName = .Range("A" & Firstrow).Value & "_" & .Range("B" & Firstrow).Value
End With

MsgBox SectionName

ActiveWorkbook.Names.Add Name:=SectionName, RefersTo:=Range("C" & Firstrow & ":BM" & EndRow), Visible:=True

End Sub[/VBA]

What appears in the message box?

[zhileezy]

i got it! it's silly mistake. The content of range ("A"& firstrow) is a number so I couldn't name the range after it. Thanks for all the help btw your code is definitely more robust and efficient than mine too!