Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to define a Named Range in VBA?
I'm trying to define a named range in VBA using the following syntax.
However, it gives me an error regarding the equal sign and if I remove the equal sign, then the named range is defined but it treats the formula as a text value so it doesn't evaluate it. I'm sure there's something easy that I'm overlooking but what is it? Any help is appreciated. ActiveWorkbook.Names.Add Name:="PivotRange", RefersToR1C1:= _ "=OFFSET(Sheet1!$A$1,0,0,COUNTA(Shee1!A:A),10) '" -- Richard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to define a Named Range in VBA?
You can't use A1 notation in an R1C1 formula, and you have a spurious '
ActiveWorkbook.Names.Add Name:="PivotRange", RefersTo:= _ "=OFFSET(Sheet1!$A$1,0,0,COUNTA(Shee1!A:A),10) " -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Richard" wrote in message ... I'm trying to define a named range in VBA using the following syntax. However, it gives me an error regarding the equal sign and if I remove the equal sign, then the named range is defined but it treats the formula as a text value so it doesn't evaluate it. I'm sure there's something easy that I'm overlooking but what is it? Any help is appreciated. ActiveWorkbook.Names.Add Name:="PivotRange", RefersToR1C1:= _ "=OFFSET(Sheet1!$A$1,0,0,COUNTA(Shee1!A:A),10) '" -- Richard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to define a Named Range in VBA?
The OP also has a typo in the countA argument (assuming Shee1 should be
Sheet1) ActiveWorkbook.Names.Add Name:="PivotRange", RefersTo:= _ "=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!A:A),10 )" -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... You can't use A1 notation in an R1C1 formula, and you have a spurious ' ActiveWorkbook.Names.Add Name:="PivotRange", RefersTo:= _ "=OFFSET(Sheet1!$A$1,0,0,COUNTA(Shee1!A:A),10) " -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Richard" wrote in message ... I'm trying to define a named range in VBA using the following syntax. However, it gives me an error regarding the equal sign and if I remove the equal sign, then the named range is defined but it treats the formula as a text value so it doesn't evaluate it. I'm sure there's something easy that I'm overlooking but what is it? Any help is appreciated. ActiveWorkbook.Names.Add Name:="PivotRange", RefersToR1C1:= _ "=OFFSET(Sheet1!$A$1,0,0,COUNTA(Shee1!A:A),10) '" -- Richard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to define a Named Range in VBA?
Thank you both for the quick responses. The typo was my error as I replaced
the actual name w/ Sheet1 for simplicity purposes when posting. The R1C1 reference I was not aware of but makes perfect sense. I've fixed my code and it works fine. Thanks again!! -- Richard "Tom Ogilvy" wrote: The OP also has a typo in the countA argument (assuming Shee1 should be Sheet1) ActiveWorkbook.Names.Add Name:="PivotRange", RefersTo:= _ "=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!A:A),10 )" -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... You can't use A1 notation in an R1C1 formula, and you have a spurious ' ActiveWorkbook.Names.Add Name:="PivotRange", RefersTo:= _ "=OFFSET(Sheet1!$A$1,0,0,COUNTA(Shee1!A:A),10) " -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Richard" wrote in message ... I'm trying to define a named range in VBA using the following syntax. However, it gives me an error regarding the equal sign and if I remove the equal sign, then the named range is defined but it treats the formula as a text value so it doesn't evaluate it. I'm sure there's something easy that I'm overlooking but what is it? Any help is appreciated. ActiveWorkbook.Names.Add Name:="PivotRange", RefersToR1C1:= _ "=OFFSET(Sheet1!$A$1,0,0,COUNTA(Shee1!A:A),10) '" -- Richard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I have a cell value define a dynamic named range? | Excel Worksheet Functions | |||
How to Define a named Formula at the Worksheet level? | Excel Worksheet Functions | |||
Define a range based on another named range | Excel Worksheet Functions | |||
Automatically define series of named ranges | Excel Programming | |||
Help with named constants in Define Name dialog box!!!! | Excel Programming |