|
参与一下。。。。
- Sub ykcbf() '//2024.4.15
- Application.ScreenUpdating = False
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- For Each sht In Sheets
- If InStr(sht.Name, "明细") Then
- st = Left(sht.Name, 2)
- With sht
- r = .Cells(Rows.Count, 1).End(3).Row
- arr = .[a1].Resize(r, 8)
- End With
- For i = 3 To UBound(arr)
- rq = Replace(arr(i, 8), ".", "/")
- s1 = arr(i, 2) & "|" & arr(i, 3) & "|" & arr(i, 4) & "|" & arr(i, 5) & "|" & arr(i, 6)
- If Not d1.exists(s1) Then Set d1(s1) = CreateObject("Scripting.Dictionary")
- d1(s1)(st) = d1(s1)(st) + arr(i, 7)
- s = s1 & "|" & st & "|" & rq
- d(s) = d(s) + arr(i, 7)
- Next
- End If
- Next
- ReDim Brr(1 To d1.Count, 1 To 10)
- For Each k In d1.keys
- m = m + 1
- b = Split(k, "|")
- Brr(m, 1) = m
- For x = 0 To UBound(b)
- Brr(m, x + 2) = b(x)
- Next
- For Each kk In d1(k).keys
- t = d1(k)(kk)
- If kk = "入库" Then Brr(m, 8) = t
- If kk = "出库" Then Brr(m, 9) = t
- Next
- Brr(m, 10) = Brr(m, 8) - Brr(m, 9)
- Next
- With Sheets("订单汇总表")
- .UsedRange.Offset(1) = ""
- .[a2].Resize(m, 10) = Brr
- c = .UsedRange.Columns.Count
- arr = .[a1].Resize(m + 1, c)
- For i = 2 To UBound(arr)
- For j = 11 To UBound(arr, 2)
- s = arr(i, 2) & "|" & arr(i, 3) & "|" & arr(i, 4) & "|" & arr(i, 5) & "|" & arr(i, 6) & "|" & "出库" & "|" & arr(1, j)
- If d.exists(s) Then
- arr(i, j) = d(s)
- End If
- Next
- Next
- .[a1].Resize(m + 1, c) = arr
- End With
- Set d = Nothing
- Set d1 = Nothing
- Application.ScreenUpdating = True
- MsgBox "OK!"
- End Sub
复制代码
|
评分
-
1
查看全部评分
-
|