|
Sub 对比合并()
Dim arr, brr, crr(1 To 1000, 1 To 7)
Application.ScreenUpdating = False
t = Timer
arr = Sheets("系统").Range("a1").CurrentRegion
brr = Sheets("盘点数量").Range("a1").CurrentRegion
Set d = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
Columns("I:O").Clear
For i = 2 To UBound(arr)
d(arr(i, 2)) = i '物料代码与行号一一对应
Next i
For i = 2 To UBound(brr)
d2(brr(i, 2)) = d2(brr(i, 2)) & "," & i '将代码与行号对应
Next i
For Each k In d.keys
If d2.exists(k) Then
x = 1
h = d(k)
For j = 1 To 3
crr(x, j) = arr(h, j)
Next j
s1 = arr(h, 3)
ar = Split(d2(k), ",")
For i = 1 To UBound(ar)
h = ar(i)
crr(x, 4) = brr(h, 2)
crr(x, 5) = brr(h, 1)
crr(x, 6) = brr(h, 3)
s2 = s2 + crr(x, 6)
x = x + 1
Next i
crr(x, 4) = k & "汇总"
crr(x, 6) = s2
crr(x, 7) = s2 - s1
End If
r = Cells(Rows.Count, 12).End(xlUp).Row
r = IIf(r = 1, 1, r + 2)
Cells(r, 9).Resize(1, 7) = Array("物料长代码", "物料短代码", "系统数量", "货号", "架位", "盘点数量", "盈亏")
Cells(r + 1, 9).Resize(x, 7) = crr
Cells(r + 1, 9).Resize(x).Merge
Cells(r + 1, 10).Resize(x).Merge
Cells(r + 1, 11).Resize(x).Merge
Range(Cells(r, 9), Cells(r + x, 15)).Borders.LineStyle = 1
Columns("I:O").AutoFit
s2 = 0
Next k
Application.ScreenUpdating = True
MsgBox Format(Timer - t, "0.00秒")
End Sub
|
|