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.
1
Function sbMatchCheck(r As Range) As Variant
2
' sbMatchCheck analyzes the worksheet formula
3
' in cell r and warns if a Match or H/Vlookup formula
4
' with last argument True, 1 or similar has been used
5
' and if the data (lookup) area is not sorted ascending.
6
' Return codes:
7
' 0 = Nothing to check, comparison parameter is 0 or
8
'     data area is sorted
9
' #Value! = Match or H/Vlookup function with last parameter 1
10
'     and unsorted data area
11
' Reverse("moc.LiborPlus.www") V0.11 PB 23-Feb-2014
12
Dim blFirst As Boolean
13
Dim sFormula() As String
14
Dim lMatchType As Long ' Can be Integer but I only use Long
15
Dim lPar As Long ' Can be Integer but I only use Long
16
Dim v As Variant, vOld As Variant
17
sFormula = Split(r.Formula & ",1)", ",")
18
sFormula(0) = Left(sFormula(0), InStr(sFormula(0) & "(", "(") - 1)
19
Select Case sFormula(0)
20
    Case "=VLOOKUP", "=HLOOKUP"
21
        lPar = 3
22
    Case "=MATCH"
23
        lPar = 2
24
    Case Else
25
        ' Neither Match nor Vlookup function: nothing to check
26
        sbMatchCheck = 0
27
        Exit Function
28
End Select
29
If sFormula(lPar) = ")" Then
30
    sFormula(lPar) = "0)"
31
End If
32
lMatchType = Sgn(Evaluate(Left(sFormula(lPar), _
33
    InStr(sFormula(lPar), ")") - 1)))
34
If lMatchType <> 0 Then
35
    blFirst = True
36
    If UBound(sFormula) = lPar Then
37
        sFormula(1) = Left(sFormula(1), Len(sFormula(1)) - 1)
38
    End If
39
    For Each v In Range(sFormula(1))
40
        If blFirst Then
41
            vOld = v
42
            blFirst = False
43
        Else
44
            If (v > vOld And lMatchType < 0) Or _
45
                (v < vOld And lMatchType > 0) Then
46
                sbMatchCheck = CVErr(xlErrValue)
47
                Exit Function
48
            End If
49
            vOld = v
50
        End If
51
    Next v
52
Else
53
    ' Last parameter is False / 0: nothing to check
54
    sbMatchCheck = 0
55
End If
56
End Function
Copied!

sbLongRandSumN

sbMostFrequentPairs

Last modified 2yr ago

Copy link