|
Sub Macro1()
Dim arr, brr, crr, d, d2, i&, zf$
Set d = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
arr = Sheet1.Range("a1").CurrentRegion
Sheet2.Activate
brr = Range("a1").CurrentRegion
For i = 2 To UBound(arr)
zf = arr(i, 1) & "," & arr(i, 2) & "," & arr(i, 5)
d(zf) = arr(i, 4)
If arr(i, 5) <> "已回款" Then d2(arr(i, 1) & "," & arr(i, 2)) = ""
Next
ReDim crr(1 To d2.Count, 1 To UBound(brr, 2))
a = d2.keys
For i = 0 To d2.Count - 1
x = Split(a(i), ",")
crr(i + 1, 1) = x(0)
crr(i + 1, 2) = x(1)
For j = 3 To UBound(brr, 2)
zf = a(i) & "," & brr(1, j)
crr(i + 1, j) = d(zf)
Next
Next
Range("a2").Resize(UBound(crr), UBound(crr, 2)) = crr
Range("a1").Resize(UBound(crr) + 1, UBound(crr, 2)).Sort [a1], Header:=xlGuess
End Sub
|
|