Adding a named range

[Stephen Boulet]

I'm trying to add a named, dynamic range. This doesn't work:

myString = "OFFSET('Data'!$A$1,0,0,COUNTA('Data'!$A:$A),"_
& VBA.Str(column_count) & ")"
ActiveWorkbook.Names.Add Name:="dataset", RefersToR1C1:=myString

The problem is that, in the resulting named range, the formula is:

="OFFSET('Data'!$A$1,0,0,COUNTA('Data'!$A:$A), 6)"

instead of

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),6)

Looks like Excel took the liberty of adding quotation marks around the
string for the range formula. Is there any way to suppress the quotation
marks or otherwise fix?

Thanks.

Stephen

 

[Tom Ogilvy]

Change

RefersToR1C1:=myString

to

RefersTo:=MyString

 

[Stephen Boulet]

Thanks Tom! It was a combination of what you suggest plus changing:

myString = "OFFSET('Data'!$A$1,0,0,COUNTA('Data'!$A:$A),"_
& VBA.Str(column_count) & ")"

to

myString = "=OFFSET('Data'!$A$1,0,0,COUNTA('Data'!$A:$A),"_
& VBA.Str(column_count) & ")"

(The difference is the extra equals sign before the word "OFFSET".)

Stephen