- Sub test()
- Dim i%, j%, k%, m%, d, arr
- Set d = CreateObject("Scripting.Dictionary")
- arr = Sheet2.[a1].CurrentRegion
- For i = 2 To UBound(arr)
- d(arr(i, 3) & "," & arr(i, 4)) = arr(i, 7)
- Next
- With Sheet1
- i = .[c65536].End(3).Row
- j = .[zz6].End(xlToLeft).Column
- arr = .Range(.[b6], .Cells(i, j))
- For k = 2 To UBound(arr)
- For m = 9 To UBound(arr, 2) Step 4
- If d.exists(arr(k, 2) & "," & arr(1, m)) Then
- arr(k, m) = d(arr(k, 2) & "," & arr(1, m))
- End If
- Next
- Next
- .Range(.[b6], .Cells(i, j)) = arr
- End With
- End Sub
复制代码 |