Adding a named range

  • Thread starter Thread starter Stephen Boulet
  • Start date Start date
S

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