|
发表于 2017-6-2 10:12
|
显示全部楼层
本楼为最佳答案
按列汇总,先用公式,再转成数值。
- Sub zz()
- Dim d, arr, brr
- Set d = CreateObject("Scripting.Dictionary")
- arr = Sheets("明細").Range("A1:AT" & Sheets("明細").[b654321].End(3).Row)
- For i = 4 To UBound(arr)
- For j = 35 To 46
- x = arr(i, 2) & arr(3, j)
- d(x) = d(x) + arr(i, j)
- Next
- Next
- With Sheets("金額分析")
- .[C3:N32] = ""
- rmax = .[b65536].End(3).Row
- brr = .Range("B2:O" & rmax)
- For i = 2 To UBound(brr)
- For j = 2 To 13
- x = brr(i, 1) & brr(1, j)
- brr(i, j) = d(x)
- S = S + d(x)
- Next
- If S > 0 Then brr(i, j) = S: S = 0
- Next
- .Range("B2:O" & .[b65536].End(3).Row) = brr
- .Cells(rmax + 1, 2) = "合计"
- .Cells(rmax + 1, 3).Resize(1, UBound(brr, 2) - 1).Formula = "=sum(r2c:r[-1]c)" '按月用公式计算
- .Cells(rmax + 1, 3).Resize(1, UBound(brr, 2) - 1) = .Cells(rmax + 1, 3).Resize(1, UBound(brr, 2) - 1).Value '公式换成数值
- End With
- End Sub
复制代码 |
评分
-
查看全部评分
|