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.

Thanks.
Howard

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
Next

End Sub

Hi Howard,

Am Sun, 25 May 2014 22:55:58 -0700 (PDT) schrieb L. Howard:

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.

try:

Sub MyNameCoder()
Dim j As String
Dim i As Long

j = "mon"

With ActiveSheet
For i = 3 To 100
.Names.Add Name:=j & "_" & i, _
RefersTo:=.Range(.Cells(i, "D"), .Cells(i, "L"))
Next
End With

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Mon, 26 May 2014 09:07:13 +0200 schrieb Claus Busch:

Sub MyNameCoder()

or modify the sheet names and the day names and name all sheets at once:

Sub MyNameCoder()
Dim j As String, strSh As String
Dim arrJ As Variant, ArrSh As Variant
Dim i As Long, n As Long

j = "Mon,Tue,Wed,Thu,Fri"
arrJ = Split(j, ",")
strSh = "Sheet1,Sheet2,Sheet3,Sheet4,Sheet5"
ArrSh = Split(strSh, ",")

For n = LBound(ArrSh) To UBound(ArrSh)
With Sheets(ArrSh(n))
For i = 3 To 100
.Names.Add Name:=arrJ(n) & "_" & i, _
RefersTo:=.Range(.Cells(i, "D"), .Cells(i, "L"))
Next
End With
Next

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Monday, May 26, 2014 12:07:13 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sun, 25 May 2014 22:55:58 -0700 (PDT) schrieb L. Howard:



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.



try:



Sub MyNameCoder()

Dim j As String

Dim i As Long



j = "mon"



With ActiveSheet

For i = 3 To 100

.Names.Add Name:=j & "_" & i, _

RefersTo:=.Range(.Cells(i, "D"), .Cells(i, "L"))

Next

End With



End Sub





Regards

Claus B.



Hi Claus,

Is there a way to only include the cells in columns D, F, H, J, L instead of D through L?

The cells E, G, I, K, M will be in another named range and subject to a formula referring to those ranges.

Howard

Hi Howard,

Am Mon, 26 May 2014 00:32:51 -0700 (PDT) schrieb L. Howard:

Is there a way to only include the cells in columns D, F, H, J, L instead of D through L?

sorry, I have to clean my glasses

Try:
Sub MyNameCoder()
Dim j As String, strSh As String
Dim arrJ As Variant, ArrSh As Variant
Dim i As Long, n As Long

j = "Mon,Tue,Wed,Thu,Fri"
arrJ = Split(j, ",")
strSh = "Sheet1,Sheet2,Sheet3,Sheet4,Sheet5"
ArrSh = Split(strSh, ",")

For n = LBound(ArrSh) To UBound(ArrSh)
With Sheets(ArrSh(n))
For i = 3 To 100
.Names.Add Name:=arrJ(n) & "_" & i, _
RefersTo:=Application.Union(.Cells(i, "D"), .Cells(i, "F"),
..Cells(i, "H"), _
.Cells(i, "J"), .Cells(i, "L"))
Next
End With
Next

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

No apologies necessary, except maybe from me for posting so much today.

I ran the five sheet code (the first one) and worked like a charm.

I'll give the modified one a whirl. A huge time saver indeed.

Thanks for all the help, hate to be a burden.

Howard

On Monday, May 26, 2014 12:58:43 AM UTC-7, L. Howard wrote:
No apologies necessary, except maybe from me for posting so much today.



I ran the five sheet code (the first one) and worked like a charm.



I'll give the modified one a whirl. A huge time saver indeed.



Thanks for all the help, hate to be a burden.



Howard


I do have another question, I notice the Scope of all the named ranges are Mon, Tue, Wed etc.

On a master sheet I am trying to use =SMALL(Mon_3,1) and it errors w/ #NAME but works fine with the same formula on the Mon sheet.

I assume the names need to be Workbook scope...? Is that possible with code?

Howard