Excel里有Vlookup函数,查找匹配数据非常方便,在VBA里,也可以用worksheetfunction.vlookup方法来调用,今天我介绍一个另一种使用字典(dictionary)+数组的方法,速度也非常快。

数据源图片:

代码:
test() test()
        Dim data, temp, arr
        Dim d
        Dim i&, k&
        Set d = CreateObject("scripting.dictionary")
        data = [a1].CurrentRegion
        For i = 2 To UBound(data)
                d(data(i, 1) & "") = data(i, 2)
        Next
        temp = [d1].CurrentRegion
        ReDim arr(2 To UBound(temp), 1 To 1)
        For k = 2 To UBound(temp)
                arr(k, 1) = d(temp(k, 1))
        Next
        [e2].Resize(UBound(arr) - 1, 1) = arr
        Set d = Nothing
End Sub




作者:laoyebin(Paladin.lao)
个人网站英文出处:http://mrvsto.com/
个人网站中文出处:http://cn.mrvsto.com/