Code to name numerous ranges

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

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.

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.
Click to expand...

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 again,

Am Mon, 26 May 2014 09:07:13 +0200 schrieb Claus Busch:
Sub MyNameCoder()
Click to expand...

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.
 
Hi Howard,



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






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.
Click to expand...


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?
Click to expand...

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.
 
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
 
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
Click to expand...


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
 
Hi Howard,

Am Mon, 26 May 2014 01:22:29 -0700 (PDT) schrieb L. Howard:
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.
Click to expand...

you can put the sheet name in front:
=SMALL(Sheet5!Fri_10,1)

Or you make global scope names:

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
ActiveWorkbook.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.
 
you can put the sheet name in front:

=SMALL(Sheet5!Fri_10,1)



Or you make global scope names:

Regards

Claus B.

--
Click to expand...

Small addition to the code with: ActiveWorkbook.

Will make it much easier to install formulas on the master sheet.

Thanks.

Howard
 
Small addition to the code with: ActiveWorkbook.

Will make it much easier to install formulas on the master sheet.

Thanks.

Howard
Click to expand...

I disagree! Using global scope will almost certainly cause name
conflicts if you need to copy sheets to another workbook. (Whether the
names ref the copied sheets or not since, by default, all global scope
names tow along with any sheet from that workbook!) Also, since global
scope isn't required to use the name in formulas then it's just NOT
'best practice' to give them global scope!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Also, since global
scope isn't required to use the name in formulas then it's just NOT

'best practice' to give them global scope!
Click to expand...

This is why I thought it was better/easier with a Global scope, and I understand you reason for best practices.

=SMALL(Mon_3,1) - Scope = Sheet

=SMALL(Sheet5!Mon_3,1) - Scope = Sheet + manually add sheet name

=SMALL(Mon_3,1) - Scope = Global

First formula errors.

Second formula is extremely tedious adding sheet name, but works fine.

Third formula works fine and is no more effort to enter than first formula.

Frustrating to me is the layout of data on the Mon to Fri sheets has changed and now a simpler formula makes more sense.

=IF(Mon!D3="","",Mon!D3)

Which can be pulled down and across so is much easier/quicker to install.

Howard
 
Also, since global
This is why I thought it was better/easier with a Global scope, and I
understand you reason for best practices.

=SMALL(Mon_3,1) - Scope = Sheet

=SMALL(Sheet5!Mon_3,1) - Scope = Sheet + manually add sheet name

=SMALL(Mon_3,1) - Scope = Global

First formula errors.
Click to expand...
It will if the name is not defined on the sheet this formula is on.
Code would also error if trying to ref a name that doesn't exist on the
active sheet. This is why we encourage using 'fully qualified' refs as
a 'best practice'!
Frustrating to me is the layout of data on the Mon to Fri sheets has
changed and now a simpler formula makes more sense.

=IF(Mon!D3="","",Mon!D3)

Which can be pulled down and across so is much easier/quicker to
install.
Click to expand...

If you select the entire range to receive the formula, type it only
once into the active cell, then Ctrl+Enter, all selected cells will
have the formula. This is the same as specifying a range in code and
assigning it a formula...

Range("A2:D2").Formula = "=A1+10"

...and selecting the range to manually enter the formula in A2 followed
by Ctrl+Enter. All pulling down does is change the row#, and pulling
across changes the column label. Ctrl+Enter causes that to happen
automatically!

Deeming adding the sheet name as 'tedious' is not an acceptable
'excuse' for wreckless usage of global scope. The sheetname is only
required when referencing the named range from another sheet, just as
it is with code when referencing a range on any sheet other than the
active sheet.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top