|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub test()
- Dim r%, i%
- Dim arr, brr(1 To 2, 13 To 16) As Double
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("sheet1")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- For j = 13 To 16
- brr(1, j) = .Cells(2, j)
- brr(2, j) = .Cells(2, j)
- Next
- i = 3
- Do While .Cells(i, 8) <> ""
- If .Cells(i, 3) = .Cells(i - 1, 3) Then
- If .Cells(i, 8) = .Cells(i - 1, 8) Then
- For j = 13 To 16
- brr(1, j) = brr(1, j) + .Cells(i, j)
- Next
- Else
- .Rows(i).Insert shift:=xlDown
- .Cells(i, 8) = "本月合计"
- .Cells(i, 13).Resize(1, 4) = Application.Index(brr, 1, 0)
- .Rows(i + 1).Insert shift:=xlDown
- .Cells(i + 1, 8) = "本年累计"
- .Cells(i + 1, 13).Resize(1, 4) = Application.Index(brr, 2, 0)
- For j = 13 To 16
- brr(1, j) = .Cells(i + 2, j)
- Next
- i = i + 2
- End If
- For j = 13 To 16
- brr(2, j) = brr(2, j) + .Cells(i, j)
- Next
- Else
- .Rows(i).Insert shift:=xlDown
- .Cells(i, 8) = "本月合计"
- .Cells(i, 13).Resize(1, 4) = Application.Index(brr, 1, 0)
- .Rows(i + 1).Insert shift:=xlDown
- .Cells(i + 1, 8) = "本年累计"
- .Cells(i + 1, 13).Resize(1, 4) = Application.Index(brr, 2, 0)
- For k = 1 To 2
- For j = 13 To 16
- brr(k, j) = .Cells(i + 2, j)
- Next
- Next
- i = i + 2
- End If
- i = i + 1
- Loop
- .Cells(i, 8) = "本月合计"
- .Cells(i, 13).Resize(1, 4) = Application.Index(brr, 1, 0)
- .Cells(i + 1, 8) = "本年累计"
- .Cells(i + 1, 13).Resize(1, 4) = Application.Index(brr, 2, 0)
- End With
- End Sub
复制代码 |
|