Match UDF
Public Function Match_UDF(RngValue2Find As Range, Rng2Search As Range, Optional BolCaseSensitive As Variant) As Range
On Error GoTo Match_UDF_ErrorHandler
Dim RngArea As Range
Dim RngCell As Range
Dim VarValue2Find As Variant
‘====================================================================================================
‘Return NOTHING iff the RngValue2Find is itself NOTHING and EXIT
‘====================================================================================================
If RngValue2Find Is Nothing Then
Set Match_UDF = Nothing
Exit Function
End If
‘====================================================================================================
‘Return NOTHING iff the RngValue2Find has more than 1 AREA and EXIT
‘====================================================================================================
If RngValue2Find.Areas.Count <> 1 Then
Set Match_UDF = Nothing
Exit Function
End If
‘====================================================================================================
‘Return NOTHING iff the RngValue2Find has more than 1 CELL and EXIT
‘====================================================================================================
If RngValue2Find.Cells.Count <> 1 Then
Set Match_UDF = Nothing
Exit Function
End If
‘====================================================================================================
‘Return NOTHING iff the Rng2Search is itself NOTHING and EXIT
‘====================================================================================================
If Rng2Search Is Nothing Then
Set Match_UDF = Nothing
Exit Function
End If
‘====================================================================================================
‘Set the default to CASE INSENSITIVE iff needs be
‘====================================================================================================
If IsMissing(BolCaseSensitive) Then
BolCaseSensitive = False
End If
‘====================================================================================================
‘Make a note of the value to be found
‘====================================================================================================
VarValue2Find = RngValue2Find.Value
‘====================================================================================================
‘If CASE INSENSITIVITY is the setting then uppercase the value to find
‘====================================================================================================
If Not BolCaseSensitive Then
RngValue2Find = UCase(RngValue2Find)
End If
‘====================================================================================================
‘Cycle through each of the AREAS in the range to seach and ALL of the cells therein
‘====================================================================================================
For Each RngArea In Rng2Search.Areas
For Each RngCell In RngArea.Cells
‘====================================================================================================
‘iff the cell to be found is actually found then return it’s single cell range location and exit
‘====================================================================================================
If BolCaseSensitive Then
If RngCell.Value = RngValue2Find Then
Set Match_UDF = RngCell
Exit Function
End If
‘====================================================================================================
’iff the [UCASE] cell to be found is actually found [case insensitive match] return it’s single cell range location & exit
‘====================================================================================================
Else
If UCase(RngCell.Value) = RngValue2Find Then
Set Match_UDF = RngCell
Exit Function
End If
End If
Next RngCell
Next RngArea
Exit Function
‘====================================================================================================
‘At this point an error of some sort has occurred and thus return NOTHING
‘====================================================================================================
Match_UDF_ErrorHandler:
Set Match_UDF = Nothing
End Function
