Excel VBA 字典/数组 示例

A 将数组作为字典的Value

Private Sub CommandButton3_Click()
Dim arr, arr1, i&, d As Object
arr = Sheets(3).[a1].CurrentRegion
arr1 = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
  d(arr(i, 1)) = ""
Next i
For i = 2 To UBound(arr1)
  If d.exists(arr1(i, 1)) Then d(arr1(i, 1)) = Array("", arr1(i, 3), "", arr1(i, 5), arr1(i, 6))
Next i
Sheets(3).[b2].Resize(d.Count, UBound(arr, 2) - 1) = Application.Transpose(Application.Transpose(d.items))
End Sub

B法
Private Sub CommandButton1_Click()
arr = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
  d(arr(i, 1)) = i
Next i
brr = Sheet3.Range("a1").CurrentRegion
For i = 2 To UBound(brr)
  n = d(brr(i, 1))
  brr(i, 3) = arr(n, 3)
  brr(i, 5) = arr(n, 5)
  brr(i, 6) = arr(n, 6)
Next
Sheet3.Range("a1").CurrentRegion = brr
End Sub

c = Array(3, 5, 6)
For i = 2 To UBound(brr)
  n = d(brr(i, 1))
  For j = 0 To 2
    brr(i, c(j)) = arr(n, c(j))
  Next
Next
原贴
http://club.excelhome.net/thread-1057654-1-1.html
http://club.excelhome.net/thread-698873-1-1.html