Variable string for Range - Names.Add

[Aurillius]

Hi,
I've tried searching through the forum but can't find a solution for the issue that I'm having.

In Excel VBA (2007), I've created a routine to dymamically build a range and save into a string. Once the entire range is collected, I've attempted to create a named range but get a runtime 1004. I'd like to know if there is a different way to do this:

Dim cRange As String
Dim MSLZ As String

'process to gather the ranges
'MSLZ = "A0010101"
'cRange = "=$B$2:$B$14,$C$2:$C$14,$D$6:$D$21,$E$6:$E$21"
ActiveWorkbook.Names.Add Name:=MSLZ, RefersTo:=cRange

Thanks,
Mark

 

[PHV]

You should add the sheet name in the cRange string.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Aurillius]

I just realized the problem. "A0010101" probably fails as it looks like a cell reference. If I use "_A0010101" it works.