How do I use VBA to find which named ranges a cell belongs to?

[Guest]

I'm tring to find code which allows me to verify which named ranges any
single cell belongs to

 

[Guest]

Give this a try...

Sub test()
Dim nme As Name
Dim rng As Range

Set rng = Range("D4")

For Each nme In ThisWorkbook.Names
If Not Intersect(rng, nme.RefersToRange) Is Nothing Then MsgBox
nme.Name
Next nme
End Sub

 

[Guest]

To the OP
To avoid 1004 errors, you might add code check that the two ranges are on
the same worksheet before try to intersect them.

For example
Intersect(worksheets(1).Rows(1),worksheets(2).columns(1)).Address

raises a 1004 error.

Another approach is to error trap. Here is some code I posted back in
October 1999 (slightly modified) that demonstrates that approach:

Sub TestName()
Dim rng As Range
definedName as Name
On Error Resume Next
For Each definedName In ThisWorkbook.Names
Set rng = Nothing
Set rng = definedName.RefersToRange
If Not rng Is Nothing Then
If Not Intersect(rng, ActiveCell) Is Nothing Then
MsgBox ActiveCell.Address & " is in a range named: " & definedName.Name
End If
End If
Next
End Sub

This has the advantage that it accounts for defined names that are not
ranges as well. (a defined name does not have to refer to a range).

 

[Guest]

I think you need to add Dim (or the line wrapped)...

Dim definedName As Name

Otherwise it is better code in all respects.

 

[Guest]

Thanks for the addendum.