How to define a Named Range in VBA?

[Richard]

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)'"

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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)"

 

[Richard]

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!!