|
Option Compare Text
Option Explicit
Sub DictAndArray() '据13楼图片
Dim ar, br, d As Object, i As Long, j As Long, y As Long
Set d = CreateObject("Scripting.Dictionary")
With Worksheets(2)
br = .Range("B4:D" & .Cells(.Rows.Count, 4).End(xlUp).Row)
End With
With Worksheets(1)
y = .Cells(.Rows.Count, 4).End(xlUp).Row
.Range("B3:C" & y).ClearContents
ar = .Range("B3:D" & y)
End With
For i = 1 To UBound(ar)
If Len(ar(i, 3)) Then d(ar(i, 3)) = i
Next
For i = 1 To UBound(br)
y = d(br(i, 3))
If y Then
For j = 1 To 2
ar(y, j) = br(i, j)
Next
End If
Next
Worksheets(1).Range("B3").Resize(UBound(ar), 2) = ar
Set d = Nothing
End Sub |
|