vba Return Name of range selected cell in in

[Chas17]

I have 12 named ranges on a sheet, ArrM1 thru ArrM12.
Each named range is same size, 6 Rows by 7 columns.

Q. If user selects a cell on sheet that is in one of these ranges, what is code to return name of range?
e.g. ArrM1 is cells c10:i15, user selects cell d12; I would like vba to return the name of the range "ArrM1".

Reason, I have the code below so if user selects cell in ArrM1 code is executed, but rather than reproduce code 12 times for each range I thought I could first see where user selects and then change variable for vrange,
Set vrange = wksYearlyCalendar.Range("ArrM1") to
Set vrange = wksYearlyCalendar.Range("ArrM" & x)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CalDaySel As String, CalDateSel As String
Dim vrange As Range
Dim cell As Range

Set vrange = wksYearlyCalendar.Range("ArrM1")

For Each cell In Target
    If Union(cell, vrange).Address = vrange.Address Then
        If ActiveCell = "" Then
            MsgBox "Please Select a valid date", , "Blank Day Selected"
            Exit Sub
        End If
  
        CalDaySel = ActiveCell
        CalDateSel = Application.WorksheetFunction.Index(wksFormulas.Range("startDates"), wksYearlyCalendar.Range("ArrNum1")) + CalDaySel - 1

        wksYearlyCalendar.Range("CalSelDayDate") = CalDateSel
        wksYearlyCalendar.Range("chascalWeekSelNote") = CalDateSel & " is in Week "

    End If
Next cell

End Sub

Thanks,
Chas

 

[gauntletxg]

Try

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

 

[Chas17]

Recieve error 1004 method range of object
on line
Set rng = Intersect(ActiveCell, Range(nm.Name))

 

[gauntletxg]

woops, try

Dim nm As Name, rng As Range
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

 

[Chas17]

If I open a new book and try it it works, but try to use in the workbook I am making it gets same error.

The ranges are arrays, contain array formulas for dates in month

 

[gauntletxg]

Do you mean array constants?

 

[Chas17]

each cell in the named range contains the same array formula

 

[gauntletxg]

Add the line Debug.Print nm.Name right after the loop starts. Then step through the loop and find which name it errors out on, and tell me what that name refers to.

 

[mikerickson]

if every cell of ArrM1 (i.e. C10:I15 ) contains the same array formula and only the cells in ArrM1 contain that formula, then

MsgBox Range("D12").CurrentArray.Name.Name

will return "ArrM1", but will error if D12 is not in a named range or if it does not contain an array formula.


If the match between array formula and named range is not perfect, then a function like this could be used.

Function RangeToNameString(inputRange As Range) As String
    Dim oneName As Name
    
    RangeToNameString = "no name"
    
    For Each oneName In ActiveWorkbook.Names
        With oneName
            If TypeName(Evaluate(.RefersTo)) = "Range" Then
                If Not (Application.Intersect(inputRange, .RefersToRange) Is Nothing) Then
                    RangeToNameString = .Name
                    ' Exit Function: Rem optional
                End If
            End If
        End With
    Next oneName
End Function

The optional Exit Function will cause different results when there are intersecting names
eg. if, in addition to ArrM1 refering to Sheet1!$C$10:$I$15, there is also a name "DynamicRange" which
RefersTo: = OFFSET(Sheet1!$D$1,0,0,COUNTA(Sheet1!$D:$D),1)

 

[SomeKindaGreekGod]

I used:

Range("A1").CurrentRegion.Name.name

to get the Named Range that cell A1 is in. Note that if the cell is not within a named range you'll get an 1004 error.