Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Names Collection Index (XL 97 and >)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Loungers...

    I have this situation:
    In my single-worksheet workbook I have 8 named Ranges on the worksheet. They all happen to be alphabetically arranged so that the first named range starts with an A and then the second named range starts with a B and the third starts with a D, and so on.

    I sometimes need to skip from one named Range to the one before it, so I wanted to see if I can take advantage of this alphabetical arrangement.

    So is there a way that I could capture the Index of the Named Range's name I am working with?
    That would then allow me to skip to the one before it by doing something like:
    name.index -1! <img src=/S/lightbulb.gif border=0 alt=lightbulb width=15 height=15>

    Thanks for all your help.

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Names Collection Index (XL 97 and >)

    Wassim,

    As it happens, the Names collection appears to be organised alphabetically, The following code will list all names in the current workbook.

    For i = 1 To ActiveWorkbook.Names.Count
    Debug.Print i, ActiveWorkbook.Names(i).Name
    Next

    Complications can arise if more than one sheet share the same name.

    Andrew

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Names Collection Index (XL 97 and >)

    You can return the index number something like this:

    a = ThisWorkbook.Names.Item(1).Index

    You can also use the range name (I believe) in place of the "1" above. Maybe you can refer to the index number in this fashion.

    Edit: Oops, didn't see you there Andrew!

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Names Collection Index (XL 97 and >)

    Thats ok Michael, in fact you made the useful point of using the Rangename as the index, so that something like :

    a = ThisWorkbook.Names.Item("NameX").Index
    Range(ThisWorkbook.Names(a - 1).Name).Select

    should achieve what Wassim is lokking for, where NameX is current rangename, and he wants to go to previous rangename.

    Andrew.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •