×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to find all the names of a cell?
2

thread770-290110

How to find all the names of a cell?

How to find all the names of a cell?

(OP)
I have a spreadsheet where certain cells have been given multiple names.  Is there a way of easily showing all the names that refer to a given cell?

In this instance, the spreadsheet deals with relay settings, and for instance zone 1 phase mho distance reach may be named Z1_, Z1P, M1P, and perhaps others.  This is to facilitate macros that can then use the setting as named for that particular relay.  I can see the name that comes first alphabetically in the box at the left end of the formula bar, but is there an easy way to see all the names?

At this point I use ^G (go to) to test to see if a name relates to the cell I think it might.  The Insert|Name|Define method is very cumbersome given that there are probably already 1000+ named ranges and many are mind numbingly similar (great for macros, really bad for skimming through lists), and it is sorted by range name, not by cell.

RE: How to find all the names of a cell?

The Range.Listnames method will write a list of names, starting in the named cell, e.g.:

Range("a1").ListNames

It puts the range name in the chosen column, and the address in the next column, so you could either sort on the address column, or read it back into VBA and do what you want with it there.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: How to find all the names of a cell?

Easy?  No.

You can get the list of names and the range that each name refers to by looping through the Names collection.

Names(index).name
Names(index).refersto
or
Names(index).RefersToRange.Address

Life gets complicated because named ranges can be more than one cell.

You can test for multi-cell ranges with

names(index).RefersToRange.Cells.Count

and you can use Intersect to test if a specific cell is within a range.

So it's possible, and left to the interested student to devise a solution.

RE: How to find all the names of a cell?

The following will work if you have only one sheet or only names belonging to one sheet. I causes an error when it tests a name from another sheet because intersection doesn't like comparing different sheets.  Prior to the If Not... it needs another "If..." to check if ThisName is within the same sheet at mycell and if not, it should not attempt to do the intersection check.

CODE

Function FindNamedRanges(mycell As Range)
Stop
Dim ThisName As Name
For Each ThisName In Names
  If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then
    Debug.Print ThisName.Name + "- Address:" + ThisName.RefersTo
  
  End If
     

Next ThisName

End Function
 

=====================================
(2B)+(2B)'  ?

RE: How to find all the names of a cell?

By the way the intersection idea was of course MintJuleps. I tried to implement it but fell slightly short.

=====================================
(2B)+(2B)'  ?

RE: How to find all the names of a cell?

OK I think this will work

CODE

Function FindNamedRanges(mycell As Range)
    Dim ThisName As Name
    For Each ThisName In Names
        If ThisName.RefersToRange.Parent Is mycell.Parent Then
            If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then
                Debug.Print ThisName.Name + "- Address:" + ThisName.RefersTo
            End If
        End If
    Next ThisName
End Function
 

=====================================
(2B)+(2B)'  ?

RE: How to find all the names of a cell?

Actually it's not set up to run as a function. Change it from a function to a sub and run it from the vba interactive editor and view the output from debug.print statements in the immediate window.   

=====================================
(2B)+(2B)'  ?

RE: How to find all the names of a cell?

Pete - nice function/sub, but I never understand why everyone uses debug.print when there is a spreadsheet available.  I have modified the code to work as a UDF

CODE

Function FindNamedRanges(mycell As Range)
    Dim ThisName As Name, NameList(1 To 100, 1 To 2) As Variant, i As Long

    i = 1
    For Each ThisName In Names
        If ThisName.RefersToRange.Parent Is mycell.Parent Then
            If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then
                NameList(i, 1) = ThisName.Name
                NameList(i, 2) = ThisName.RefersTo
                i = i + 1
            End If
        End If
    Next ThisName
    FindNamedRanges = NameList
End Function

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: How to find all the names of a cell?

I built on Pete's work too.  This works as a function, returning a comma separated list of names that refer to the cell that the function is pointed at.

CODE

Function FindMyNames(mycell As Range) As String
    Dim ThisName As Name
    Dim Answer As String
    Answer = ""
   
   If mycell.Cells.Count > 1 Then
        Answer = "One Cell Only Please"
        
    Else
        
        For Each ThisName In Names
            If ThisName.RefersToRange.Parent Is mycell.Parent Then
                If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then
                    Answer = Answer & ThisName.Name & ", "
                End If
            End If
        Next ThisName
    
    End If
    FindMyNames = Answer
End Function

RE: How to find all the names of a cell?

I just tidied mine up to get rid of the nasty hard coded array size :)

CODE

Function FindNamedRanges(mycell As Range)
    Dim ThisName As Name, NameList() As String, i As Long, NumRows As Long

    NumRows = Application.Caller.Rows.Count
    ReDim NameList(1 To NumRows, 1 To 2)
    i = 1
    For Each ThisName In Names
        If ThisName.RefersToRange.Parent Is mycell.Parent Then
            If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then
                NameList(i, 1) = ThisName.Name
                NameList(i, 2) = ThisName.RefersTo
                i = i + 1
                If i > NumRows Then Exit For
            End If
        End If
    Next ThisName
    FindNamedRanges = NameList
End Function

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: How to find all the names of a cell?

Quote:

I never understand why everyone uses debug.print when there is a spreadsheet available.

Why output to debug window?
Because it's easier. (one line of code vs 6 lines of code you used to replace it)... outputs right to where I'm developing my program.
Not better, just easier. Thanks you guys for fixing it up to provide a better output.
 

=====================================
(2B)+(2B)'  ?

RE: How to find all the names of a cell?

(OP)
Thanks all, but I seem to be particularly dense this morning.  Where do I put the function (any of them) and how do I call it?  I've tried a few things, but without success.

RE: How to find all the names of a cell?

(OP)
OK, turns out my problem is the use of multiple sheets and Intersect throwing an error.  Fighting other battles today, I get back to this in a day or two.

RE: How to find all the names of a cell?

The intersection wasn't supposed to execute unless the names were in the same sheet "If ThisName.RefersToRange.Parent Is mycell.Parent Then.."

Apparently that didn't work.  The check assumes that the parent of name refers to a sheet.   That should usually be the case, but maybe for some reason for structure of your spreadsheet it's not?  (parent is workbook... or chart...?).

If you get the error again, try to examine which names are being examined and what are the parents of those names (to figure out why the check for different sheets didn't work).
 

=====================================
(2B)+(2B)'  ?

RE: How to find all the names of a cell?

David,

You can do it without VBA.
In Excel 2007 follow menu commands:
Formulas/Use in Formula/Paste Names.../Paste List
In Excel 2003: Insert/Names/Paste... (somewhere there).

It will display a table of all names and their addresses. Sort by addresses to identify the names corresponding to the same address.

Yakpol

RE: How to find all the names of a cell?

(OP)
Thank you.  Much nicer than the macros.

RE: How to find all the names of a cell?

Another simple approach which seems like it would be helpful in large complicated spreadsheets like you describe is the "trace dependents" feature.

For Excel 2000, with the cell in question selected, select Tools/Auditing/TraceDependents.

It will show arrows to all the other cells in same sheet that reference the selected cell (regardless of whether those other cells refer to selected cell by name or by address).  It will also show an arrow to a little sheet icon and if you click on that arrow you see a list of all the cell addresses in other sheets that refer to your selected cell.  

Then Tools/Auditing/RemoveAllArrows to get rid of the arrows

=====================================
(2B)+(2B)'  ?

RE: How to find all the names of a cell?

(OP)
In my case, all the references are from other VBA macros, using excel as a way of moving data between programs that know nothing of each other but have export and import capabilities where excel is used to convert the data of the export file to the import file.  Read it, place it, then write it.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login

Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now
Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close