Check name of Named range of selected cells

[julhs]

I don’t think I can use Intersect because the selected range does have a Named range specifically for it, but the selected range will also be part of another Named range
Eg

Select cells are "C97:C98" and Range Name = "NameMe"
But I have another Range Name, "NamesAll" that refers to "C2:C100"

So due to the Intersect problem I thought the below would give me what I’m after.
From https://stackoverflow.com/questions...u-get-a-specific-name-associated-with-a-range

However, it bugs out with;
Error 1004
Application-defined or object-defined error

Sub TestAddress()
Dim n As Name
For Each n In ActiveWorkbook.Names
If n.RefersToRange.Address = "C97:C98" Then 'This line bugs out
Debug.Print n.Name
End If
Next
End Sub

 

[smozgur]

The Address property returns the address as an absolute reference - "$C$97:$C$98". Therefore, you should either pass RowAbsolute and ColumnAbsolute parameters as False as I did in the following code or use the address property of the range referencing to the address.

Sub TestAddress()
  Dim n As Name
  For Each n In ActiveWorkbook.Names
    If n.RefersToRange.Address(False, False) = "C97:C98" Then
    'If n.RefersToRange.Address = Range("C97:C98").Address Then
      Debug.Print n.Name
    End If
  Next
End Sub

However, this doesn't explain the error.
The actual reason should be a named reference that doesn't actually referring to a range but returning a value - like =TODAY(). Check the names in the workbook and you'll see what I mean. In this case, since RefersToRange property won't return "nothing" (unfortunately) but simply generate a fatal error, you might need an error handler like shown below.

Sub TestAddress()
Dim n As Name
    On Error Resume Next
    For Each n In ActiveWorkbook.Names
        If n.RefersToRange.Address(False, False) = "C97:C98" Then
            If Err.Number = 0 Then
                Debug.Print n.Name
            Else
                Err.Clear
            End If
        End If
    Next
    On Error GoTo 0
End Sub

One more note: if you actually will use the selection address instead of hard coding the range address as string then you might also want to verify if the name is actually in the active worksheet. Here is the updated code for the worksheet verification.

Sub TestAddress()
Dim n As Name
    On Error Resume Next
    For Each n In ActiveWorkbook.Names
        If n.RefersToRange.Address(False, False) = "C97:C98" Then
            If Err.Number = 0 Then
                If n.RefersToRange.Parent.Name = ActiveSheet.Name Then
                   Debug.Print  n.Name
                End If
            Else
                Err.Clear
            End If
        End If
    Next
    On Error GoTo 0
End Sub

 

[julhs]

Thanks for response snozgur
You have given me several different options.
Give me time to test things out.

 

[julhs]

Your post was very informative and plenty to get my head around.
Have opted to go with your 3rd suggestion because of,

verify if the name is actually in the active worksheet

I don’t want to hard code the range ("C97:C98") because THIS Sub will be called by another routine that will have already selected anyone of a number of named ranges.
I tweaked things slightly.

Sub TestAddress()
Dim n As Name
   On Error Resume Next
   For Each n In ActiveWorkbook.Names
       If n.RefersToRange.Address = Selection.Address Then ' Changed this line slightly
          If Err.Number = 0 Then
             If n.RefersToRange.Parent.Name = ActiveSheet.Name Then
                Debug.Print n.Name
            End If
        Else
           Err.Clear
      End If
   End If
Next
On Error GoTo 0
End Sub

I do have a follow up question
Just as a backup and to future proof things, how can I incorporate a Msgbox to say "Selection is not a valid NamedRange." , I’ve tried doing it but not figured it out.

 

[smozgur]

Sure, Selection.Address is the correct change in your case.

Try this for the alert (assuming that you wouldn't have multiple names that are referencing to the exact same ranges):

Sub TestAddress()
Dim n As Name
   On Error Resume Next
   For Each n In ActiveWorkbook.Names
       If n.RefersToRange.Address = Selection.Address Then ' Changed this line slightly
          If Err.Number = 0 Then
             If n.RefersToRange.Parent.Name = ActiveSheet.Name Then
                Debug.Print n.Name
                ' The named range found. No need to execute more, right?
                Exit Sub
            End If
        Else
           Err.Clear
      End If
   End If
Next
' If the execution arrived this far, it means no named range found.
MsgBox "Selection is not a valid NamedRange.", vbOkOnly + vbInformation
On Error GoTo 0
End Sub

 

[julhs]

Fantastic, that deals with my supplementary question.
Pushing your patience now!!
What is behind your statement;

(assuming that you wouldn't have multiple names that are referencing to the exact same ranges):

What if I do have multiple names that are referencing to the exact same ranges?
Reason for question is because I do and see the Debug.Print only shows one of them, is there a way to show both?

 

[smozgur]

What is behind your statement;

Because I assumed there is only one named range referring to the same range. It was a wrong assumption, apparently.

In this case, we need to check if there is at least one match and we can do that by using a boolean variable called found.

Sub TestAddress()
Dim n As Name
Dim found As Boolean
   On Error Resume Next
   For Each n In ActiveWorkbook.Names
       If n.RefersToRange.Address = Selection.Address Then ' Changed this line slightly
          If Err.Number = 0 Then
             If n.RefersToRange.Parent.Name = ActiveSheet.Name Then
                Debug.Print n.Name
                ' We have at least one match
                found = True
            End If
        Else
           Err.Clear
      End If
   End If
Next
On Error GoTo 0
If Not found Then
    MsgBox "Selection is not a valid NamedRange.", vbOKOnly + vbInformation
End If
End Sub

 

[julhs]

Thank you so much, you have come up trumps again!
As a by the way this link,
Is cell value the name used as Named range name
is something @Skyybot gave me that dealt with that query, has similarities to currant thread.
But realised later I had completely overlooked certain aspects, hence need for this one.
Many thanks for your help.
Julhs

 

[smozgur]

Thank you so much, you have come up trumps again!
As a by the way this link,
Is cell value the name used as Named range name
is something @Skyybot gave me that dealt with that query, has similarities to currant thread.
But realised later I had completely overlooked certain aspects, hence need for this one.
Many thanks for your help.
Julhs

You're welcome.
Glad to hear it helps. Thanks for the feedback.