Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to define range names
I have a list of dates associated with Week Nbrs starting in column B, C & D.
Several formulas link to a summary table on another worksheet which counts occurances for a particular week and several years. I have figured out how to write an If statement using data validation so that the user can see a list of dates associated with the weekly summary labeled "Week 1" but it requires me to define names for each week. Example Name: Week_1_2009 = $B$4:$B$7 ect... This list, below, goes on until the end of 2012. I was hoping to be able to write a macro that will define all these names for me without having to physically define each name....I don't know how to do that....Can anyone help? Calendar Date Year Week Nbr 1/1/2009 2009 1 1/2/2009 2009 1 1/3/2009 2009 1 1/4/2009 2009 1 1/5/2009 2009 2 1/6/2009 2009 2 1 -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to define range names
without knowing all your ranges tad difficult. But as an idea, I turned macro
recorder on and added your example and got this: Sub Macro1() ActiveWorkbook.Names.Add Name:="Week_1_2009", RefersToR1C1:= _ "= Sheet1!R4C2:R7C2" End Sub if you copy & paste the code bit as many times as needed & updated the range reference as required - you would then only need to run macro once to add all the names. Just an idea - hope helpful -- jb "Carrie_Loos via OfficeKB.com" wrote: I have a list of dates associated with Week Nbrs starting in column B, C & D. Several formulas link to a summary table on another worksheet which counts occurances for a particular week and several years. I have figured out how to write an If statement using data validation so that the user can see a list of dates associated with the weekly summary labeled "Week 1" but it requires me to define names for each week. Example Name: Week_1_2009 = $B$4:$B$7 ect... This list, below, goes on until the end of 2012. I was hoping to be able to write a macro that will define all these names for me without having to physically define each name....I don't know how to do that....Can anyone help? Calendar Date Year Week Nbr 1/1/2009 2009 1 1/2/2009 2009 1 1/3/2009 2009 1 1/4/2009 2009 1 1/5/2009 2009 2 1/6/2009 2009 2 1 -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to define range names
I was hoping for something not so labor intensive but I did end up using your
suggestion. Thanks john wrote: without knowing all your ranges tad difficult. But as an idea, I turned macro recorder on and added your example and got this: Sub Macro1() ActiveWorkbook.Names.Add Name:="Week_1_2009", RefersToR1C1:= _ "= Sheet1!R4C2:R7C2" End Sub if you copy & paste the code bit as many times as needed & updated the range reference as required - you would then only need to run macro once to add all the names. Just an idea - hope helpful I have a list of dates associated with Week Nbrs starting in column B, C & D. Several formulas link to a summary table on another worksheet which counts [quoted text clipped - 16 lines] 1/6/2009 2009 2 1 -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to define range names
Hi Carrie,
Excel has a weeknum() function in the analysis toolpak addin. (toolsaddins...) There are also several ISOweeknum functions. A google search should bring up several dozen. John "Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message news:95fb3eea42b9c@uwe... I have a list of dates associated with Week Nbrs starting in column B, C & D. Several formulas link to a summary table on another worksheet which counts occurances for a particular week and several years. I have figured out how to write an If statement using data validation so that the user can see a list of dates associated with the weekly summary labeled "Week 1" but it requires me to define names for each week. Example Name: Week_1_2009 = $B$4:$B$7 ect... This list, below, goes on until the end of 2012. I was hoping to be able to write a macro that will define all these names for me without having to physically define each name....I don't know how to do that....Can anyone help? Calendar Date Year Week Nbr 1/1/2009 2009 1 1/2/2009 2009 1 1/3/2009 2009 1 1/4/2009 2009 1 1/5/2009 2009 2 1/6/2009 2009 2 1 -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Define Range Names in Excel 2003-SP2 | Excel Discussion (Misc queries) | |||
define max in range (macro) | Excel Discussion (Misc queries) | |||
How do I use a Macro to define a Named Range | Excel Programming | |||
how to define range names | New Users to Excel | |||
Macro to Define Range Name | Excel Programming |