Determine the range where the active cell is

[girapas]

I use the code given as reply in thread:

http://www.mrexcel.com/forum/showthread.php?t=353649&page=2

When I change specific range with ActiveCell I've got error: Method 'Intersect' of object '_Global' failed.

Any help?

 

[Colin Legg]

Hi,

You're getting that error because it is trying to get the intersect of two ranges on different sheets. The code has been writen with an On Error Resume Next statement to handle that eventuality. Go to Tools > Options > General tab and under the 'Error Trapping' section, select 'Break in Class Module' rather than 'Break on All Errors'.


For reference, the code MikeRickson posted on that other thread is this:

Dim rangeOfInterest As Range
Dim oneName As Name
Set rangeOfInterest = Range("B2:B3")

For Each oneName In ThisWorkbook.Names
    On Error Resume Next
    If Application.Intersect(oneName.RefersToRange, rangeOfInterest) Is Nothing Then
        Rem not in named Range
    Else
        MsgBox rangeOfInterest.Address & " is in the named range " & oneName.Name
        Exit For
    End If
Next oneName

 

[girapas]

Thank you for the reply...