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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
>
>
>