How to find the range names that includes a Cell

[kunal_shrivastava]

Hello All,

Can we determine the range names that includes a cell??
Lets say i name a Range A1:C3 as "R_3_3_1"
Then i name a Range A1:A9 as "R_1_9_1"

Now can we determine from VBA that cell A1 is included in ranges "R_3_3_1" and "R_1_9_1"
and cell A8 is in only "R_1_9_1"
and cell C4 is not in any of the ranges..


Thanks in Advance
Kunal

 

[Andrew Poulsom]

You can return the name of a range like this:

MsgBox Range("A1:A3").Name.Name

but you have to use the entire range. To determine if a cell is within a range you can use the Intersect method:

MsgBox (Not Application.Intersect(Range("A1"), Names("R_3_3_1").RefersToRange) Is Nothing)

 

[MrKowz]

To expand a bit, if you want a list of the cells that overlap in each range, you can use:

Public Sub NamedRangeIntersects()
Dim IntersectRNG            As Range, _
    rng                     As Range, _
    IntersectAddr           As String
    
Set IntersectRNG = Intersect(Range("R_3_3_1"), Range("R_1_9_1"))
If Not IntersectRNG Is Nothing Then
    For Each rng In IntersectRNG
        IntersectAddr = IntersectAddr & rng.Address(False, False) & ", "
    Next rng
    IntersectAddr = Left(IntersectAddr, Len(IntersectAddr) - 2)
End If
MsgBox IntersectAddr
End Sub

 

[kunal_shrivastava]

Thanks All,
I got it....
Infact the below method that does my work...
Sub Find_Names()
For Each n In ActiveWorkbook.Names
Set y = Intersect(ActiveCell, Range(n.RefersTo))
If Not y Is Nothing Then MsgBox "Cell is in : " & n.Name
Next
End Sub