Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TugboatEng on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to find all the names of a cell? 2

Status
Not open for further replies.
davidbeach

davidbeach

Electrical
Joined
Mar 13, 2003
Messages
9,551
Location
US
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.
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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)' ?
 
Upvote 0 Downvote
By the way the intersection idea was of course MintJuleps. I tried to implement it but fell slightly short.

=====================================
(2B)+(2B)' ?
 
Upvote 0 Downvote
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)' ?
 
Upvote 0 Downvote
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)' ?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
I never understand why everyone uses debug.print when there is a spreadsheet available.
Click to expand...

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)' ?
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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)' ?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Thank you. Much nicer than the macros.
 
Upvote 0 Downvote
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)' ?
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dik
  • Locked
  • Question Question
Convert Cell Types
Replies
10
Views
1K
dik
dik
Zimmy242
  • Question Question
Static Pressure Loss of Duct Fitting
Replies
2
Views
3K
Snickster
Snickster
Kenja824
  • Locked
  • Question Question
excel Formula Find help
Replies
5
Views
1K
Kenja824
Kenja824
electricpete
  • Locked
  • Question Question
an update to vba routine for highlighting the current cell
Replies
6
Views
487
skeletron
skeletron
vinodnx7
  • Locked
  • Question Question
count the same text from the file and create new list with number
Replies
19
Views
2K
vinodnx7
vinodnx7

Part and Inventory Search

Sponsor

Back
Top