Results 1 to 8 of 8
  1. #1
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find location of name (2000+)

    Two quick (I hope) questions relating to names in VBA:

    1] Is it possible to determine if the ActiveCell has a Name?

    2] Is it possible to determine the location of a named cell? Failing that, can one define an offset to (e.g.) insert data into the cell to the right of a named cell?

    Hope this makes sense.
    TIA

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

    Re: Find location of name (2000+)

    1) I think you'd have to loop through the collection of names and check the RefersTo or RefersToRange property of each name (see under 2) to see if it points to the active cell.

    2) You can use ActiveWorkbook.Names("MyName").RefersTo to find the address of the named range MyName in the form of a formula: =Sheet1!$B$5.
    And ActiveSheet.Range("MyName") refers to the named range itself. So you could use

    ActiveSheet.Range("MyRange").Offset(0, 1) = "Testing 123"

    to insert a value into the cell to the right of the cell named MyRange.

  3. #3
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find location of name (2000+)

    Thanks Hans,

    I've since found that using ActiveCell.Name.Name is usable, albeit giving an error if the cell has no name.

    And the offset will work perfectly <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    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: Find location of name (2000+)

    1] The method Hans alludes to can be found at Chip Pearson's site on Named ranges. There is a section on "Determining The Name Of A Cell Or Range" which has a procedure for finding a name [names can intercept more than one range, this finds the first one] which contains the cell name or a procedure which finds only names that matches the range completely.

    Steve

  5. #5
    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: Find location of name (2000+)

    To get around the error you could modify the code:

    <pre>Option Explicit
    Sub ShowName()
    Dim Cell_Name As String
    <font color=red> On Error Resume Next
    Cell_Name = "The cell has no name"</font color=red>
    Cell_Name = ActiveCell.Name.Name
    <font color=red> On Error GoTo 0</font color=red>
    MsgBox Cell_Name
    End Sub</pre>


    Note: this method will only give a name that is the exact match (and is the same method that Pearson used. the other method of Pearson will give also give a name when the cell is contained in a named range...

    Steve

  6. #6
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find location of name (2000+)

    Useful link - thanks very much!

  7. #7
    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: Find location of name (2000+)

    Chip Pearson's site is filled with great code examples and primers on excel topics.

    Steve

  8. #8
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find location of name (2000+)

    And yet more thanks - duly bookmarked <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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