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.
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
Last modified 2yr ago