【VBA案例001】实现VLOOKUP功能

1
'Option Explicit
2

3
Sub 单元格循环()
4
    Dim cel As Range
5
    Dim cel2 As Range
6
    Dim t As Double
7
    t = Timer
8
    [e6:e13] = ""
9
    For Each cel In Range("a6:a13")
10
        For Each cel2 In Range("d6:d13")
11
            If cel.Value = cel2.Value Then
12
                cel2.Offset(0, 1).Value = cel.Offset(0, 1).Value
13
                Exit For
14
            End If
15
        Next
16
    Next
17
    Debug.Print Format(Timer - t, "0.00000000000000s")
18
End Sub
19

20
Sub 数组循环()
21
    Dim ar, br
22
    Dim t As Double
23
    t = Timer
24
    [e6:e13] = ""
25
    ar = [a6:b13] 'range("a6:b13")
26
    br = [d6:e13]
27

28
    Dim i, j
29
    For i = 1 To UBound(ar)
30
        For j = 1 To UBound(ar)
31
            If ar(i, 1) = br(j, 1) Then
32
                br(j, 2) = ar(i, 2)
33
                Exit For
34
            End If
35
        Next j
36
    Next i
37

38
    [d6:e13] = br
39
    Debug.Print Format(Timer - t, "0.00000000000000s")
40
End Sub
41

42
Sub 字典循环()
43
    Dim d As Object, kw$
44
    Set d = CreateObject("Scripting.Dictionary")
45
    'd.CompareMode = vbTextCompare '不区分大小写
46
    Dim ar, br
47
    Dim t As Double
48
    t = Timer
49
    [e6:e13] = ""
50
    ar = [a6:b13] 'range("a6:b13")
51
    br = [d6:e13]
52

53
    Dim i, j
54
    For i = 1 To UBound(ar)
55
        d(ar(i, 1)) = ar(i, 2) 'KEY ITEM
56
    Next i
57

58
    For j = 1 To UBound(br)
59
        br(j, 2) = d(br(j, 1))
60
    Next j
61

62
    [d6:e13] = br
63
    Debug.Print Format(Timer - t, "0.00000000000000s")
64
End Sub