|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
看倒数第三行代码。- Sub 合并汇总()
- Application.DisplayAlerts = False
- Columns("G:G").Delete
- Columns("G:G").Insert
- r = [b65536].End(3).Row
- For i = 3 To r '辅助列,部门前的数字
- Cells(i, "G") = IIf(Val(Cells(i, 2)) > 0, Val(Cells(i, 2)), Cells(i, 2))
- Next
-
- r1 = 3
- For i = 3 To r '根据辅助列排序
- If Cells(i, 2) = "小计" Then
- r2 = i - 1
- Range(Cells(r1, 2), Cells(r2, "G")).Sort key1:=Cells(r1, "G")
- r1 = i + 1
- End If
- Next
-
- Cells(2, "G") = "金额合计" 'G列填充
- For i = 3 To r + 1
- Cells(i, "G") = Cells(i, 4) + Cells(i, 6)
- Next
-
- For i = r To 3 Step -1 '合并单元格
- If Val(Cells(i - 1, 2)) = Val(Cells(i, 2)) And Val(Cells(i, 2)) <> 0 Then
- Cells(i - 1, "G") = Cells(i - 1, "G") + Cells(i - 1, "G").Offset(1, 0)
- Range(Cells(i - 1, "G"), Cells(i, "G")).Merge
- Cells(i - 1, "G").Interior.ColorIndex = 35
- End If
- Next
- Cells(1, "G").Resize(r + 1, 1).Borders.LineStyle = 1 '边框线
- Cells(3, "G").Resize(r + 1, 1).NumberFormatLocal = "#,##0.00_ "
- Application.DisplayAlerts = True
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|