sbMatchCheck

Sometimes unexperienced Excel © users apply a Match or Vlookup or Hlookup worksheet function with a last parameter True or 1 (this is the comparison type which expects a sorted lookup area), but their lookup area is not sorted ascending.
The UDF below is checking this.
Please read my Disclaimer.
Function sbMatchCheck(r As Range) As Variant
' sbMatchCheck analyzes the worksheet formula
' in cell r and warns if a Match or H/Vlookup formula
' with last argument True, 1 or similar has been used
' and if the data (lookup) area is not sorted ascending.
' Return codes:
' 0 = Nothing to check, comparison parameter is 0 or
'     data area is sorted
' #Value! = Match or H/Vlookup function with last parameter 1
'     and unsorted data area
' Reverse("moc.LiborPlus.www") V0.11 PB 23-Feb-2014
Dim blFirst As Boolean
Dim sFormula() As String
Dim lMatchType As Long ' Can be Integer but I only use Long
Dim lPar As Long ' Can be Integer but I only use Long
Dim v As Variant, vOld As Variant
sFormula = Split(r.Formula & ",1)", ",")
sFormula(0) = Left(sFormula(0), InStr(sFormula(0) & "(", "(") - 1)
Select Case sFormula(0)
    Case "=VLOOKUP", "=HLOOKUP"
        lPar = 3
    Case "=MATCH"
        lPar = 2
    Case Else
        ' Neither Match nor Vlookup function: nothing to check
        sbMatchCheck = 0
        Exit Function
End Select
If sFormula(lPar) = ")" Then
    sFormula(lPar) = "0)"
End If
lMatchType = Sgn(Evaluate(Left(sFormula(lPar), _
    InStr(sFormula(lPar), ")") - 1)))
If lMatchType <> 0 Then
    blFirst = True
    If UBound(sFormula) = lPar Then
        sFormula(1) = Left(sFormula(1), Len(sFormula(1)) - 1)
    End If
    For Each v In Range(sFormula(1))
        If blFirst Then
            vOld = v
            blFirst = False
        Else
            If (v > vOld And lMatchType < 0) Or _
                (v < vOld And lMatchType > 0) Then
                sbMatchCheck = CVErr(xlErrValue)
                Exit Function
            End If
            vOld = v
        End If
    Next v
Else
    ' Last parameter is False / 0: nothing to check
    sbMatchCheck = 0
End If
End Function

sbLongRandSumN

sbMostFrequentPairs

Last modified 2yr ago

Copy link