Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically defining dynamic range
For awhile now, I have been using code which creates a set of Range names
from a table list which provides individual range names along with the Row and Cell numbers to be used in the refers to assignment statements. However, as I add new named ranges to the list I have to manually change my code which creates/recreates the named range that refers to that list to account for the new items. I have run accross the =OFFSET Method which also employs the CountA function to create a dynamically sizable range. However, I can only get that to work by manually creating the range name through the insert/name menu option. The following is the code I'm trying to use. It runs, but without creating the range name. It does not throw an error. Also, I use R1C1 notation. Any advice on this would be appreciated. ThisWorkbook.Names.Add Name:="Setup" & "!" & _ "StaticRanges", RefersTo:="=OFFSET(" & "Setup" & "!R3C30" & _ ",0,0, CountA(" & "Setup" & "!R3C30:RC30),5)", Visible:=True Thank you, Doug |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically defining dynamic range
Dynamic named ranges do not show up in the Name Box on the formula bar, so
you may find that your code is created the named range without you knowing it - which is why it is not throwing an error. Secondly you may want to include a row component to your range being counted otherwise it the range will be relative to the active cell. Try: ThisWorkbook.Names.Add Name:="Setup" & "!" & _ "StaticRanges", RefersTo:="=OFFSET(" & "Setup" & "!R3C30" & _ ",0,0, CountA(" & "Setup" & "!R3C30:R100C30),5)", Visible:=True Then type StaticRanges in the Name Box to check the result. Hope this helps Rowan "TempestFyre" wrote: For awhile now, I have been using code which creates a set of Range names from a table list which provides individual range names along with the Row and Cell numbers to be used in the refers to assignment statements. However, as I add new named ranges to the list I have to manually change my code which creates/recreates the named range that refers to that list to account for the new items. I have run accross the =OFFSET Method which also employs the CountA function to create a dynamically sizable range. However, I can only get that to work by manually creating the range name through the insert/name menu option. The following is the code I'm trying to use. It runs, but without creating the range name. It does not throw an error. Also, I use R1C1 notation. Any advice on this would be appreciated. ThisWorkbook.Names.Add Name:="Setup" & "!" & _ "StaticRanges", RefersTo:="=OFFSET(" & "Setup" & "!R3C30" & _ ",0,0, CountA(" & "Setup" & "!R3C30:RC30),5)", Visible:=True Thank you, Doug |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically defining dynamic range
Thank you, I didn't realize that they wouldn't show up in the names box, even
if the visible property has been set to true, so I assumed it wasn't created. "Rowan Drummond" wrote: Dynamic named ranges do not show up in the Name Box on the formula bar, so you may find that your code is created the named range without you knowing it - which is why it is not throwing an error. Secondly you may want to include a row component to your range being counted otherwise it the range will be relative to the active cell. Try: ThisWorkbook.Names.Add Name:="Setup" & "!" & _ "StaticRanges", RefersTo:="=OFFSET(" & "Setup" & "!R3C30" & _ ",0,0, CountA(" & "Setup" & "!R3C30:R100C30),5)", Visible:=True Then type StaticRanges in the Name Box to check the result. Hope this helps Rowan "TempestFyre" wrote: For awhile now, I have been using code which creates a set of Range names from a table list which provides individual range names along with the Row and Cell numbers to be used in the refers to assignment statements. However, as I add new named ranges to the list I have to manually change my code which creates/recreates the named range that refers to that list to account for the new items. I have run accross the =OFFSET Method which also employs the CountA function to create a dynamically sizable range. However, I can only get that to work by manually creating the range name through the insert/name menu option. The following is the code I'm trying to use. It runs, but without creating the range name. It does not throw an error. Also, I use R1C1 notation. Any advice on this would be appreciated. ThisWorkbook.Names.Add Name:="Setup" & "!" & _ "StaticRanges", RefersTo:="=OFFSET(" & "Setup" & "!R3C30" & _ ",0,0, CountA(" & "Setup" & "!R3C30:RC30),5)", Visible:=True Thank you, Doug |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically defining dynamic range
You're welcome.
"TempestFyre" wrote: Thank you, I didn't realize that they wouldn't show up in the names box, even if the visible property has been set to true, so I assumed it wasn't created. "Rowan Drummond" wrote: Dynamic named ranges do not show up in the Name Box on the formula bar, so you may find that your code is created the named range without you knowing it - which is why it is not throwing an error. Secondly you may want to include a row component to your range being counted otherwise it the range will be relative to the active cell. Try: ThisWorkbook.Names.Add Name:="Setup" & "!" & _ "StaticRanges", RefersTo:="=OFFSET(" & "Setup" & "!R3C30" & _ ",0,0, CountA(" & "Setup" & "!R3C30:R100C30),5)", Visible:=True Then type StaticRanges in the Name Box to check the result. Hope this helps Rowan "TempestFyre" wrote: For awhile now, I have been using code which creates a set of Range names from a table list which provides individual range names along with the Row and Cell numbers to be used in the refers to assignment statements. However, as I add new named ranges to the list I have to manually change my code which creates/recreates the named range that refers to that list to account for the new items. I have run accross the =OFFSET Method which also employs the CountA function to create a dynamically sizable range. However, I can only get that to work by manually creating the range name through the insert/name menu option. The following is the code I'm trying to use. It runs, but without creating the range name. It does not throw an error. Also, I use R1C1 notation. Any advice on this would be appreciated. ThisWorkbook.Names.Add Name:="Setup" & "!" & _ "StaticRanges", RefersTo:="=OFFSET(" & "Setup" & "!R3C30" & _ ",0,0, CountA(" & "Setup" & "!R3C30:RC30),5)", Visible:=True Thank you, Doug |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help defining dynamic range | Charts and Charting in Excel | |||
Defining a named range for a dynamic result set | Excel Programming | |||
Defining a Dynamic Range using a variable | Excel Programming | |||
Defining Dynamic Range | Excel Programming | |||
Defining Names Programmatically | Excel Programming |