Tags: cant, dim, excel, follows, function, lookupsomerange, microsoft, msdn, range, returns, rng, software, string

Why can't name the range?

On Microsoft » Microsoft Excel

3,981 words with 6 Comments; publish: Thu, 22 May 2008 09:22:00 GMT; (306125.00, « »)

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

All Comments

Leave a comment...

  • 6 Comments
    • 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

      #1; Thu, 22 May 2008 09:23:00 GMT
    • I also tried: rng.Name = v or rng.Name.Name = v; neither worked out.
      #2; Thu, 22 May 2008 09:24:00 GMT
    • 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 wrote:

      > 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

      --

      Dave Peterson

      #3; Thu, 22 May 2008 09:25:00 GMT
    • 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 wrote:

      > with rng

      > .name = "'" & .parent.name & "'!" & v

      > end with

      >

      #4; Thu, 22 May 2008 09:26:00 GMT
    • Figured out: the name couldn't have "-" in it!

      PerlDev wrote:

      > 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 wrote:

      > > with rng

      > > .name = "'" & .parent.name & "'!" & v

      > > end with

      > >

      #5; Thu, 22 May 2008 09:27:00 GMT
    • 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.

      PerlDev wrote:

      > 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 wrote:

      > > with rng

      > > .name = "'" & .parent.name & "'!" & v

      > > end with

      > >

      --

      Dave Peterson

      #6; Thu, 22 May 2008 09:28:00 GMT