Why can't name the range?

[PerlDev]

I try to name a range as follows:

dim rng as Range, v as String
set rng = LookupSomeRange( v ) 'where LookupSomeRange is a function,
returns range

'now name it, bur has run-time error '1004', the name is not valid

ActiveSheet.Names.add Name:= v, RefersToR1C1="""=" & ActiveSheet.Name
& "!" & v & """", Visible:=True

' i am sure the value of v is unique, there is no name conflict

What went wrong?

-PD

 

[PerlDev]

There was a typo

'now name it, bur has run-time error '1004', the name is not valid

ActiveSheet.Names.add Name:= v, RefersToR1C1="""=" & ActiveSheet.Name
& "!" & v & """", Visible:=True

with ActiveSheet
.Names.add Name:=v, RefersToR1C1="""=" & .Name & "!" &
rng.AddressLocal & """", Visible=True
end with

 

[PerlDev]

I also tried: rng.Name = v or rng.Name.Name = v; neither worked out.

 

[Dave Peterson]

with rng
.name = "'" & .parent.name & "'!" & v
end with

The apostrophes may be required depending on the worksheet name.

And if this doesn't work, what does V hold?

(Even if v is used before, your code won't mind--it'll just reuse it.)

 

[PerlDev]

Thanks for your quick response, Dave. It still doesn't work.

dim c as variant, v as String
For each c In Range("A2:A10")
v = c.value
..
with rng
.Name = "'" & .Parent.Name & "'!" & v ' still have Run-time
error '1004', that name is not valid; where v is "ABC-DEF"
end rng
next c

 

[PerlDev]

Figured out: the name couldn't have "-" in it!

Thanks for your quick response, Dave. It still doesn't work.

dim c as variant, v as String
For each c In Range("A2:A10")
v = c.value
..
with rng
.Name = "'" & .Parent.Name & "'!" & v ' still have Run-time
error '1004', that name is not valid; where v is "ABC-DEF"
end rng
next c

 

[Dave Peterson]

Yep. Your name is invalid. ABC-DEF can't be used.

dim c as Range
dim v as String
For each c In Range("A2:A10")
v = c.value
v = application.substitute(v,"-","_")
with c
.Name = "'" & .Parent.Name & "'!" & v
end with
next c

And the cell with ABC-DEF will be named ABC_DEF.

And it's "end with" not "End Rng"

And you want to use the same range variable (c, not rng).

And there are lots of other invalid names, too--not just those with hypens.

You'll want to be careful.