VBA - is this cell in a given range?

[mbahr2]

I'm struggling to find, through VBA, how one can find out if a cell is within a range, and then return that range name. For example if cell A1 is within a larger range of "A1:A20" named "RANGE1", how do you retrieve that name through code?

[NateO]

Something like EDC's test work here?

Function InRange(Range1 As Range, Range2 As Range) As Boolean
' returns True if Range1 is within Range2
Dim InterSectRange As Range
Set InterSectRange = Application.Intersect(Range1, Range2)
InRange = Not InterSectRange Is Nothing
Set InterSectRange = Nothing
End Function


Sub TestInRange()
If InRange(ActiveCell, Range("A1:D100")) Then
' code to handle that the active cell is within the right range
MsgBox "Active Cell In Range!"
Else
' code to handle that the active cell is not within the right range
MsgBox "Active Cell NOT In Range!"
End If
End Sub

HTH. Cheers, Nate

[Russell Hauf]

Try something like this:

Sub RangeNames()
    Dim nm As Name
    
    For Each nm In ActiveWorkbook.Names
        If Not Intersect(Range("C3"), nm.RefersToRange) Is Nothing Then
            MsgBox nm.Name
        End If
    Next
End Sub

Hope this helps,

Russell

[mbahr2]

I appreciate your fast reply, but I think the code you gave me assumes I know the name of the range I'm trying to find. My problem is that I need to retrieve the name of the range that the cell is located in. I know the cell will be contained in a range. I just don't know which one.

[Russell Hauf]

On 2002-02-28 08:54, mbahr2 wrote:
I appreciate your fast reply, but I think the code you gave me assumes I know the name of the range I'm trying to find. My problem is that I need to retrieve the name of the range that the cell is located in. I know the cell will be contained in a range. I just don't know which one.

See my post - it should work for you.