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

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

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

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