|Subject:||Code to name numerous ranges|
|Posted by:||L. Howard (lhkitt…@comcast.net)|
|Date:||Sun, 25 May 2014|
I have a great deal of similar and adjacent ranges to name.
Starting in D3 and down to D100 I want the first named range to be mon_3, then mon_4 and on down to D100.
Where mon is for Monday and the 3 is for the row it is in.
Mon_3 would refer to D3, F3, H3, J3, L3
Mon_4 would refer to D4, F4, H4, J4, L4
Then on the next sheet I will copy the code and change j to j = "tue".
Repeat till "Fri".
This errors out with "Object doesn't support this property". Compiles nicely, but no cigar.
Dim c As Range
Dim j As String
Dim i As Long
j = "mon"
i = 3
For Each c In Range("D3:D100")
c.Names.Add Name:=j & "_" & i, RefersTo:=Union(Cells(i, 4), Cells(i, 6), Cells(i, 8), Cells(i, 10), Cells(i, 12))
i = i + 1