|
发表于 2015-9-1 15:33
|
显示全部楼层
本楼为最佳答案
- Sub tt()
- Dim Sh As Worksheet
- Set d = CreateObject("scripting.dictionary")
- rq = Val(ActiveSheet.Name) '当前表格日期
- crr = Array(24, 25, 29, 34, 35)
- For Each Sh In Worksheets
- arr = Sh.Range("a1:an" & Sh.[a65536].End(3).Row)
- If Val(Sh.Name) > 0 And Val(Sh.Name) < rq Then '所有日期小于当前表格日期的工作表
- For i = 6 To UBound(arr)
- If arr(i, 1) = "" Then arr(i, 1) = arr(i - 1, 1)
- If arr(i, 2) = "" Then arr(i, 2) = arr(i - 1, 2)
- For j = 0 To UBound(crr)
- c = crr(j)
- x = arr(i, 1) & arr(i, 2) & arr(i, 3) & arr(4, c) '款号+款式+日期+统计项为key
- d(x) = d(x) + arr(i, c) '累加为item
- Next
- Next
- ElseIf Sh.Name = ActiveSheet.Name Then '当前表
- For i = 6 To UBound(arr)
- If arr(i, 1) = "" Then arr(i, 1) = arr(i - 1, 1)
- If arr(i, 2) = "" Then arr(i, 2) = arr(i - 1, 2)
- For j = 0 To UBound(crr)
- c = crr(j)
- x = arr(i, 1) & arr(i, 2) & arr(i, 3) & arr(4, c) '款号+款式+日期+统计项为key
- arr(i, c) = d(x) '累加为item
- Next
- Next
- For j = 0 To UBound(crr)
- c = crr(j)
- Sh.Cells(1, c).Resize(UBound(arr), 1) = Application.Index(arr, , c) '对应列填入结果
- Next
- End If
- Next
- End Sub
复制代码 |
|