Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Code to name numerous ranges

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Code to name numerous ranges

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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Code to name numerous ranges

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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Code to name numerous ranges

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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Code to name numerous ranges

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


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Code to name numerous ranges


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
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Code to name numerous ranges

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
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Code to name numerous ranges

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.


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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Code to name numerous ranges


you can put the sheet name in front:

=SMALL(Sheet5!Fri_10,1)



Or you make global scope names:

Regards

Claus B.

--


Small addition to the code with: ActiveWorkbook.

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

Thanks.

Howard
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Code to name numerous ranges


you can put the sheet name in front:

=SMALL(Sheet5!Fri_10,1)



Or you make global scope names:

Regards

Claus B.

--


Small addition to the code with: ActiveWorkbook.

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

Thanks.

Howard


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




  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Code to name numerous ranges

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


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


  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Code to name numerous ranges

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


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.

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.


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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Naming Ranges with code Les Excel Programming 2 January 19th 08 01:57 PM
VBA code for Date ranges Rook Excel Programming 4 September 4th 06 12:14 PM
VLOOKUP for Zip Code Ranges JerseyJR Excel Worksheet Functions 2 September 6th 05 06:37 PM
Problem with code about ranges cdb Excel Programming 2 March 4th 05 11:41 AM
Defining ranges in VB code Rachael Moody Excel Programming 5 January 27th 04 03:21 PM


All times are GMT +1. The time now is 01:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"