Excel里有Vlookup函数,查找匹配数据非常方便,在VBA里,也可以用worksheetfunction.vlookup方法来调用,今天我介绍一个另一种使用字典(dictionary)+数组的方法,速度也非常快。
数据源图片:
代码:
Sub 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
源文件:dictionary