Code to name numerous ranges

Giganews Newsgroups
Subject: Code to name numerous ranges
Posted by:  L. Howard (lhkitt…
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
etc., etc.

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.


Sub MyNameCoder()

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

End Sub