Results 1 to 6 of 6
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sheet Name in .address

    [img]/w3timages/icons/question.gif[/img]
    I need to get the full address (ie including the sheet name) back from a selected range.

    The following ...

    range("NamedRange").address

    returns $A$4 (if NamedRange refers to SheetName!$A$4

    BUT BUT BUT how do I get it to return

    'SheetName!$A$4???

    Can someone please help (and if it turns out to be something like ".fulladdress" I am going to scream.)

    Cheers

    Tim
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  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: Sheet Name in .address

    Dont scream but what I think you are looking for is <font color=red>ActiveSheet.Name + range("NamedRange").address</font color=red>

    Hope that works, and perhaps somebody will post a more elegant solution

    Andrew

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet Name in .address

    Try:

    <pre>Dim sW As String
    sW = Names("sheet2!Name").RefersTo
    </pre>

    Legare Coleman

  4. #4
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet Name in .address

    Of course!

    I was using 'refersto' to set the range name via the macro, I just didn't think that it would be a readable property as well.

    Now, all I need to use it in 'range().value' is strip the "=" out - easy.

    Thanks all for you help (and the speed!!)
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet Name in .address

    Actually the reply was a bit slow. I had just created a VBA module to test that when they delivered my Sushi for lunch. Your answer took second place. [img]/w3timages/icons/smile.gif[/img]

    Glad I could help.
    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet Name in .address

    This simple line of code should give you the name of the worksheet of the "NamedRange":
    Range("NamedRange").Parent.Name

Posting Permissions

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