Sulprobil
Search…
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!
Last modified 1yr ago
Copy link