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

[keithb]

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

[Max]

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

[keithb]

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

[Max]

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.

[Max]

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

[Max]

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

[Herbert Seidenberg]

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)

[keithb]

Thanks Max, your solution really saved me!

I do have one question if you don't mind:
Your solution uses a different syntax (R!C5 Vs. $E$1) as compared to the
result viewed in the Names dialog: This is your formula:

sFormula = "=OFFSET(Sheet1!R1C5,1,0,COUNTA(Sheet1!C5)-1)"

This is what gets displayed in the Define Name dialog box after clicking
your command button:

=OFFSET(Sheet1!$E$1,1,0,COUNTA(Sheet1!$E:$E)-1)

So how did you know to use the R1C5 designation to create a $E$1 in the
result?

Many thanks,

Keith



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

[Max]

Glad to hear you got it going !

> So how did you know to use the R1C5 designation
> to create a $E$1 in the result?

> sFormula = "=OFFSET(Sheet1!R1C5,1,0,COUNTA(Sheet1!C5)-1)"

Aha, I did cheat a little <g>. Tinkered around in reverse. Recorded a macro
to create the name (via Insert>Name Define) and pasted in the dynamic range
formula from your post. The equivalent formula in R1C1 style was an output
from the macro recorder.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"keithb" <[email protected]> wrote in message
news:[email protected]...
> Thanks Max, your solution really saved me!
>
> I do have one question if you don't mind:
> Your solution uses a different syntax (R!C5 Vs. $E$1) as compared to the
> result viewed in the Names dialog: This is your formula:
>
> sFormula = "=OFFSET(Sheet1!R1C5,1,0,COUNTA(Sheet1!C5)-1)"
>
> This is what gets displayed in the Define Name dialog box after clicking
> your command button:
>
> =OFFSET(Sheet1!$E$1,1,0,COUNTA(Sheet1!$E:$E)-1)
>
> So how did you know to use the R1C5 designation to create a $E$1 in the
> result?
>
> Many thanks,
>
> Keith