PDA

View Full Version : Naming ranges of cells using cell content



zhileezy
06-01-2009, 03:34 PM
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!!!:yes:yes:yes

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
06-02-2009, 01:34 AM
Welcome to the forum :hi:

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

Is this what you are trying to achieve...

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

Hope this helps

zhileezy
06-02-2009, 06:06 AM
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
06-02-2009, 06:32 AM
If you use this...

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

What appears in the message box?

zhileezy
06-02-2009, 07:21 AM
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!