Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming Ranges with code | Excel Programming | |||
VBA code for Date ranges | Excel Programming | |||
VLOOKUP for Zip Code Ranges | Excel Worksheet Functions | |||
Problem with code about ranges | Excel Programming | |||
Defining ranges in VB code | Excel Programming |