|
要把运行错误的附件发上来才知道的。可能是数组没读取到,或者Sheet3不存在之类的。用这个代码试试,新加表格用于运行计算结果。- Sub tt()
- arr = [a1].CurrentRegion
- ReDim brr(1 To UBound(arr), 1 To 6)
- Set d = CreateObject("scripting.dictionary")
- For i = 2 To UBound(arr) - 1
- x = Left(arr(i, 1), 1) & "," & arr(i, 2) & "," & arr(i, 3) & "," & arr(i, 4)
- If Not d.exists(x) Then
- n = n + 1: d(x) = n
- brr(n, 1) = Left(arr(i, 1), 1): brr(n, 2) = arr(i, 2)
- brr(n, 3) = arr(i, 3): brr(n, 4) = arr(i, 4)
- End If
- p = d(x)
- brr(p, 5) = brr(p, 5) + arr(i, 5)
- brr(p, 6) = brr(p, 6) + arr(i, 6)
- Next
- Worksheets.Add after:=Sheets(Sheets.Count)
- With ActiveSheet
- .Cells.Clear
- .[a1].Resize(1, 6) = Array("等级", "长", "宽", "厚度", "件数", "方数")
- .[a2].Resize(n, 6) = brr
- .[a2].Resize(n, 6).Sort key1:=.[a2], key2:=.[b2], key3:=.[c2]
- arr = .[a2].Resize(n + 1, 6) '排序后的源数组
- ReDim brr(1 To UBound(arr) + 100, 1 To 6)
- n = 0
- For i = 1 To UBound(arr) - 1 '分类汇总
- n = n + 1
- For j = 1 To 6: brr(n, j) = arr(i, j): Next
- s1 = s1 + arr(i, 5): s2 = s2 + arr(i, 6)
- If arr(i, 1) <> arr(i + 1, 1) Then
- n = n + 1
- brr(n, 1) = arr(i, 1) & "总计"
- brr(n, 5) = s1: brr(n, 6) = s2
- s1 = 0: s2 = 0
- End If
- Next
- .[a2].Resize(n, 6) = brr
- End With
- End Sub
复制代码 |
|