Results 1 to 3 of 3

Thread: Range Name (XP)

  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Location
    Hellertown, Pennsylvania, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range Name (XP)

    (VBA) I need to determine what range a selected cell is in. For example, if I select cell S22 and it is in Range "Test" I need to return the name of the range.
    Thanks for the help.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Range Name (XP)

    Try this function. It will find one of the names (if it contains multiple names). If you pass more than 1 cell as the range name, it will give a response if ANY of the cells are in a name. It could be modified to insist that the ENTIRE range is within the name or even that only the upper left cell. You could even write a routine to have ALL the names listed!

    Steve
    <pre>Option Explicit
    Function InName(rng As Range)
    Dim nName As Name
    Dim isect

    InName = CVErr(xlErrNA)

    For Each nName In ActiveWorkbook.Names
    If Not Intersect(rng, nName.RefersToRange) Is Nothing Then
    InName = nName.Name
    Exit Function
    End If
    Next nName
    End Function
    </pre>


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

    Re: Range Name (XP)

    The following function should return the name of the range to which given cell (or range of cells) belongs. Given that a such a range may belong to more than one named range, the function returns a list of names, where appropriate, seperated by a semicolon.

    Function RangeName(rngInput As Range) As String
    Dim oName As Name, strSep As String
    For Each oName In ActiveWorkbook.Names
    If Len(RangeName) Then strSep = "; "
    If Not Intersect(Range(oName), rngInput) Is Nothing Then
    RangeName = RangeName & strSep & oName.Name
    End If
    Next
    End Function


    EDIT :

    To allow using the function across workbooks, replace

    For Each oName In ActiveWorkbook.Names

    with

    For Each oName In rngInput.Parent.Parent.Names

    Andrew C

Posting Permissions

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