|
- Sub test()
- Dim d As Object
- Dim d1 As Object
- Dim r%, i%
- Dim arr, brr
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
-
- Application.DisplayAlerts = False
- fl = Array("701A", "702B", "703C", "704D", "705E", "706F", "707G", "708H", "709I", "710J", "711K", "712N", "713O", "714P", "715Q", "716R", "717S", "718T", "719U", "720V", "721X", "722Z")
- V = Array(1, 2, 3, 12, 21, 22)
- For i = 0 To UBound(fl)
- d(fl(i)) = Application.Match(i + 1, V, 1)
- Next
- With Worksheets("数据")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:c" & r)
- End With
- For i = 1 To UBound(arr)
- xm = Left(arr(i, 1), 4)
- If arr(i, 2) <> "缺少" Then
- d1(xm) = d1(xm) + 1
- End If
- Next
-
- With Worksheets("sheet1")
- .Cells.Delete
- .Range("a1:d1") = Array("一级", "二级", "数量", "合计")
- .Range("a2").Resize(d.Count, 2) = Application.Transpose(Array(d.Items, d.Keys))
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("b2:d" & r)
- For i = 1 To UBound(arr)
- If d1.Exists(arr(i, 1)) Then
- arr(i, 2) = d1(arr(i, 1))
- End If
- Next
- .Range("b2").Resize(UBound(arr), UBound(arr, 2)) = arr
- For i = 0 To UBound(V) - 1
- .Cells(V(i) + 1, 1) = "第" & Application.Text(i + 1, "[dbnum1]") & "类"
- .Cells(V(i) + 1, 1).Resize(V(i + 1) - V(i), 1).Merge
- .Cells(V(i) + 1, 4).FormulaR1C1 = "=SUM(RC[-1]:R[" & V(i + 1) - V(i) - 1 & "]C[-1])"
- .Cells(V(i) + 1, 4).Resize(V(i + 1) - V(i), 1).Merge
- Next
- .Cells(V(i) + 1, 1) = "第" & Application.Text(i + 1, "[dbnum1]") & "类"
- .Cells(V(i) + 1, 4).FormulaR1C1 = "=SUM(RC[-1]:R[" & r - V(i) - 1 & "]C[-1])"
- End With
- End Sub
复制代码 |
|