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.
' 0 = Nothing to check, comparison parameter is 0 or
' #Value! = Match or H/Vlookup function with last parameter 1
' Reverse("moc.LiborPlus.www") V0.11 PB 23-Feb-2014
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)
Case "=VLOOKUP", "=HLOOKUP"
' Neither Match nor Vlookup function: nothing to check
If sFormula(lPar) = ")" Then
lMatchType = Sgn(Evaluate(Left(sFormula(lPar), _
InStr(sFormula(lPar), ")") - 1)))
If UBound(sFormula) = lPar Then
sFormula(1) = Left(sFormula(1), Len(sFormula(1)) - 1)
For Each v In Range(sFormula(1))
If (v > vOld And lMatchType < 0) Or _
(v < vOld And lMatchType > 0) Then
sbMatchCheck = CVErr(xlErrValue)
' Last parameter is False / 0: nothing to check