Determine the named range by partial range name and activecell

[wbstadeli]

Hi All,

Is there a way to determine what named range my active cell is in, by matching it to part of the ranges name? Example: I have a varying amount of ranges called "Item_Range_(Variable)". In my worksheet code i want to use something that can match whichever range the "target" cell is intersecting with any of the ranges called "Item_Range_" (and a certian column number of that range) and not take in account the variable part of the range name. Is this possible?

This code is working code for a hard code name of "Item_Range_", it doesn't work for this case because it cant find that range name.

If Not Intersect(Target, Range("Item_Range_" ).Columns(7)) Is Nothing Then

 

[CephasOz]

You can use the "InRange" function below to return the range in which the active cell is sitting. I have included a "test" sub so you can see how it might be used.

Sub test()
    Dim rngFound As Range
    Set rngFound = InRange(ActiveCell)
End Sub

Function InRange(rngCell As Range) As Range
    Dim namItem As Name
    Set InRange = Nothing
    For Each namItem In ThisWorkbook.Names
        If (Left(namItem.Name, 11) = "Item_Range_") Then
            If (namItem.RefersToRange.Parent.Name = rngCell.Parent.Name) Then
                If (Not (Intersect(rngCell, namItem.RefersToRange) Is Nothing)) Then
                    Set InRange = namItem.RefersToRange
                    Exit For
                End If
            End If
        End If
    Next
End Function

 

[wbstadeli]

You can use the "InRange" function below to return the range in which the active cell is sitting. I have included a "test" sub so you can see how it might be used.

Sub test()
    Dim rngFound As Range
    Set rngFound = InRange(ActiveCell)
End Sub

Function InRange(rngCell As Range) As Range
    Dim namItem As Name
    Set InRange = Nothing
    For Each namItem In ThisWorkbook.Names
        If (Left(namItem.Name, 11) = "Item_Range_") Then
            [B][COLOR=rgb(184, 49, 47)]If (namItem.RefersToRange.Parent.Name = rngCell.Parent.Name) Then[/COLOR][/B]
                If (Not (Intersect(rngCell, namItem.RefersToRange) Is Nothing)) Then
                    Set InRange = namItem.RefersToRange
                    Exit For
                End If
            End If
        End If
    Next
End Function

Wow, this looks like it could work! I copied the code into my workbook but im getting a runtime error 1004, Application-defined or object-defined error on this line:
If (namItem.RefersToRange.Parent.Name = rngCell.Parent.Name) Then

 

[CephasOz]

The line that causes an error is the first line that uses "rngCell", so it looks like that variable is not defined. Did you copy all of the code that I wrote, or did you just copy some lines from the "InRange" function?

 

[wbstadeli]

The line that causes an error is the first line that uses "rngCell", so it looks like that variable is not defined. Did you copy all of the code that I wrote, or did you just copy some lines from the "InRange" function?

I copied the entire code, including your test sub.

 

[CephasOz]

Okay. Copy the "InRange" function below, and replace the version that you currently have. Which line throws an error?

Function InRange(rngCell As Range) As Range
    Dim namItem As Name
    Set InRange = Nothing
    For Each namItem In ThisWorkbook.Names
        If (Left(namItem.Name, 11) = "Item_Range_") Then
            MsgBox namItem.RefersToRange.Parent.Name
            MsgBox rngCell.Parent.Name
            If (namItem.RefersToRange.Parent.Name = rngCell.Parent.Name) Then
                If (Not (Intersect(rngCell, namItem.RefersToRange) Is Nothing)) Then
                    Set InRange = namItem.RefersToRange
                    Exit For
                End If
            End If
        End If
    Next
End Function

 

[wbstadeli]

Okay i did that, and i added this error line to which seemed to get us past the 1004 error (something to do with some ranges not reference ranges or invalid ranges. However the first message box pops up the sheet name, and so does the second, and "InRange" is not getting set, if i click on it it says "In-Range=nothing".

Function InRange(rngCell As Range) As Range
    Dim namItem As Name
    Set InRange = Nothing
    On Error Resume Next
    For Each namItem In ThisWorkbook.Names
        If (Left(namItem.Name, 11) = "Item_Range_") Then
            MsgBox namItem.RefersToRange.Parent.Name
            MsgBox rngCell.Parent.Name
            If (namItem.RefersToRange.Parent.Name = rngCell.Parent.Name) Then
                If (Not (Intersect(rngCell, namItem.RefersToRange) Is Nothing)) Then
                    Set InRange = namItem.RefersToRange
                    Exit For
                End If
            End If
        End If
    Next
End Function

 

[CephasOz]

It seems that you have some names of the form "Item_Range_" that don't actually refer to ranges, so the code needs to be amended to check for that, like so:

Sub test()
    Dim rngToCheck As Range
    Set rngToCheck = InRange(ActiveCell)
    If (rngToCheck Is Nothing) Then
        MsgBox "Not in Item_Range_nn"
    Else
        MsgBox rngToCheck.Address
    End If
End Sub

Function InRange(rngCell As Range) As Range
    Dim namItem As Name
    Set InRange = Nothing
    For Each namItem In ThisWorkbook.Names
        If (Left(namItem.Name, 11) = "Item_Range_") Then
            If IsRange(namItem.RefersTo) Then
                If (namItem.RefersToRange.Parent.Name = rngCell.Parent.Name) Then
                    If (Not (Intersect(rngCell, namItem.RefersToRange) Is Nothing)) Then
                        Set InRange = namItem.RefersToRange
                        Exit For
                    End If
                End If
            End If
        End If
    Next
End Function

Function IsRange(ByVal strToCheck As String) As Boolean
    On Error Resume Next
    IsRange = (Not (Range(Mid(strToCheck, 2)) Is Nothing))
End Function

 

[wbstadeli]

Thank you very much, i appreciate your help!!

 

[CephasOz]

Glad to help.