|
- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("发货明细")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:q" & r)
- End With
- For i = 1 To UBound(arr)
- If Not d.exists(arr(i, 7)) Then
- Set d(arr(i, 7)) = CreateObject("scripting.dictionary")
- End If
- d(arr(i, 7))(arr(i, 9)) = d(arr(i, 7))(arr(i, 9)) + arr(i, 16)
- Next
- With Worksheets("汇总查询")
- .UsedRange.Offset(1, 0).Clear
- m = 2
- For Each aa In d.keys
- .Cells(m, 2).Resize(d(aa).Count, 2) = Application.Transpose(Array(d(aa).keys, d(aa).items))
- .Cells(m + d(aa).Count, 2) = "小计"
- .Cells(m + d(aa).Count, 3) = Application.Sum(d(aa).items)
- With .Cells(m, 1)
- .Value = aa
- .Resize(d(aa).Count + 1, 1).Merge
- End With
- m = m + d(aa).Count + 1
- Next
- .Range("a1:c" & m - 1).Borders.LineStyle = xlContinuous
- End With
- Application.ScreenUpdating = True
- MsgBox "数据汇总完毕!"
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|