Results 1 to 6 of 6

Thread: Cell Name (03)

  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell Name (03)

    If cell A1 contains a defined range "MyRange" is it possible to determine the defined range that is assigned to cell A1?

    I'm not looking for all range names in a workbook, just one assigned to a specific cell such as A1. My example only provides the cell address.

    Range("D1") = Range("A1").Name

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Name (03)

    This need not be unique: in theory, several named ranges could refer to cell A1. Here is code that will output global names to the Immediate window:

    Sub Test()
    Dim nam As Name
    Dim wsh As Worksheet
    Dim wbk As Workbook
    Dim rng As Range
    Set wbk = ActiveWorkbook
    Set wsh = wbk.Worksheets(1)
    Set rng = wsh.Range("A1")
    For Each nam In wbk.Names
    If nam.RefersToRange = rng Then
    Debug.Print nam.Name
    End If
    Next nam
    End Sub

  3. #3
    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: Cell Name (03)

    You could add this function into a module:

    <pre>Option Explicit
    Function NamedRange(rng As Range)
    Application.Volatile
    Dim nm As Name
    For Each nm In ActiveWorkbook.Names
    If nm.RefersToRange.Parent.Name = _
    rng.Parent.Name And _
    Union(nm.RefersToRange, rng).Address = rng.Address Then
    NamedRange = nm.Name
    Exit Function
    End If
    Next
    NamedRange = CVErr(xlErrValue)
    End Function</pre>


    Then in D1 you could enter:
    <pre>=NamedRange(A1)</pre>


    If there are more than 1 name it will display the first one (I believe the one with the lowest alphabetical name). If there is not a name, it will give an #Value error.

    Steve

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Name (03)

    Hans,

    I get the concept however the Debug.Print will place each defined name in the list not just the name of the one cell.

    I have attached a sample workbook.

    Thanks,
    John

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Name (03)

    Oops <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Change

    If nam.RefersToRange = rng Then

    to

    If nam.RefersToRange.Address = rng.Address Then

    (or use Steve's code)

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Name (03)

    Hans/Steve,

    Thank you for your suggestions.

    John

Posting Permissions

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