Get Named Range Name if cell is known

[reneuend]

I know the exact cell, but I can't get VBA to return the named range that is defined for this cell. I've seem examples on other posts for doing this, but I'm getting a 1004 error: "Application-defined or Object-defined error".

I wrote code to select the cell and then ran this code.

    For Each nm In ThisWorkbook.Names
        Set rng = Intersect(ActiveCell, Range(nm.RefersToRange.Address))
        If Not rng Is Nothing Then
            MsgBox ActiveCell.Address & " is in the named range " & nm.Name
            Exit For
        End If
    Next nm

The error occurs on the line "set rng = Intersect(..........).

Any ideas on how to get the named range for a specified cell would be greatly appreciated.

Thanks!!!

 

[Jonmo1]

Not all named ranges actually refer to a range..
Therefor may not have an address
Causing this to error
Range(nm.RefersToRange.Address)

Use On Error Resume next..

On Error Resume Next
Set rng = Intersect(ActiveCell, Range(nm.RefersToRange.Address))
On Error Goto 0


Hope that helps.

 

[reneuend]

That worked!

You're awesome. Thanks!!!!!

 

[CharlesChuckieCharles]

Seems to work for me fine
Check that rng is a range if previously declared
also depending on which version of Excel you're using the scope of a Named range can be sheet or workbook

are all you're REFERENCES ok?

 

[reneuend]

Thanks CCC. all my references seem to be fine.
nm is defined as a Name type

I can't seem to find a way to determine what reference is causing an error. I'll do some more debugging to see if I can figure it out.

Anyway, using Jonmo's resume on error seems to be working.

 

[Jonmo1]

Try this crude message box..

Dim rng As Range
For Each nm In ThisWorkbook.Names
    On Error Resume Next
    Set rng = Intersect(ActiveCell, Range(nm.RefersToRange.Address))
    If Err.Number = 1004 Then MsgBox "The range named " & nm.Name & " is causing an error"
    On Error GoTo 0
    If Not rng Is Nothing Then
        MsgBox ActiveCell.Address & " is in the named range " & nm.Name
        Exit For
    End If
Next nm

 

[reneuend]

I was wrong CCC!

Using JonMo's advice, I put in the condition to see what was causing the error. There was a bad reference!

Thanks guys for taking the time to help me out!