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

 

[keithb]

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

 

[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

 

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

 

[Max]

An alternative link for the sample file:
http://www.savefile.com/files/4911406
File: keithb_gen.xls

 

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

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.

--