Help! Formula assigned to a variable yields unwanted quotation marks.

I need to assign a name to a formula. Using the code shown below, the
resulting named formula is surrounded by quotation marks and does not
execute properly when the name is used in a cell reference. Can someone
suggest an alternate syntax that will name the formula without adding
quotation marks?

sFormula = "OFFSET(Sheet1!$E$1,1,0,COUNTA(Sheet1!$E:$E)-1)"
ActiveWorkbook.Names.Add Name:="thispath", RefersToR1C1:=sFormula

Thanks,

Keith

Perhaps try instead:

sFormula = "=OFFSET(Sheet1!R1C5,1,0,COUNTA(Sheet1!C5)-1)"
ActiveWorkbook.Names.Add Name:="thispath", RefersToR1C1:= _
sFormula

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"keithb" <[email protected]> wrote in message
news:[email protected]...
> I need to assign a name to a formula. Using the code shown below, the
> resulting named formula is surrounded by quotation marks and does not
> execute properly when the name is used in a cell reference. Can someone
> suggest an alternate syntax that will name the formula without adding
> quotation marks?
>
> sFormula = "OFFSET(Sheet1!$E$1,1,0,COUNTA(Sheet1!$E:$E)-1)"
> ActiveWorkbook.Names.Add Name:="thispath", RefersToR1C1:=sFormula
>
> Thanks,
>
> Keith
>
>

Thanks for your suggestion; however, that gives me an "error in formula"
message.


"Max" <[email protected]> wrote in message
news:%[email protected]...
> Perhaps try instead:
>
> sFormula = "=OFFSET(Sheet1!R1C5,1,0,COUNTA(Sheet1!C5)-1)"
> ActiveWorkbook.Names.Add Name:="thispath", RefersToR1C1:= _
> sFormula
>
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "keithb" <[email protected]> wrote in message
> news:[email protected]...
>> I need to assign a name to a formula. Using the code shown below, the
>> resulting named formula is surrounded by quotation marks and does not
>> execute properly when the name is used in a cell reference. Can someone
>> suggest an alternate syntax that will name the formula without adding
>> quotation marks?
>>
>> sFormula = "OFFSET(Sheet1!$E$1,1,0,COUNTA(Sheet1!$E:$E)-1)"
>> ActiveWorkbook.Names.Add Name:="thispath", RefersToR1C1:=sFormula
>>
>> Thanks,
>>
>> Keith
>>
>>
>
>

You're welcome. But I don't know, seems to work ok* for me.
Maybe hang around awhile for better insights from others

*Here's a link to download a sample file
with a simple implementation to play with:
http://flypicture.com?display=updone&id=qtHzlKk=
File: keithb_gen.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"keithb" <[email protected]> wrote in message
news:[email protected]...
> Thanks for your suggestion; however, that gives me an "error in formula"
> message.

> http://flypicture.com?display=updone&id=qtHzlKk=

Try copy > paste the entire link (include the last "=" at the right)
into the browser address bar, press ENTER
(Link may not work when directly clicked)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

An alternative link for the sample file:
http://www.savefile.com/files/4911406
File: keithb_gen.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

I can duplicate your problem if I enter
OFFSET(Sheet1!$E$1,1,0,COUNTA(Sheet1!$E:$E)-1)
into the RefersTo box instead of
=OFFSET(Sheet1!$E$1,1,0,COUNTA(Sheet1!$E:$E)-1)