《vba吧提問-怎么寫每月合計的代碼》，對表格中每月合計的行進行計算

Sub 選中列每月合計()'適用單/多列選中、單/多列部分選中Dim rng As Range, first_row, last_row, first_col, last_col, col_add, sum_j, imonth_total = Array(1, "本月合計") '每月合計所在列號Set rng = Intersect(ActiveSheet.UsedRange, Selection) 'intersect語句避免選擇整列造成無用計算first_row = rng.Row '選中區域開始行號last_row = first_row + rng.Rows.count - 1 '選中區域結束行號first_col = rng.Column '選中區域開始列號last_col = first_col + rng.Columns.count - 1 '選中區域結束列號col_add = Split(Columns(first_col).Address(0, 0), ":") '選中區域開始行號字母sum_j = first_rowFor i = first_row To last_rowIf InStr(Cells(i, CInt(month_total(0))), CStr(month_total(1))) > 0 ThenCells(i, first_col).Formula = "=SUM(" & col_add(0) & sum_j & ":" & col_add(0) & i - 1 & ")"If last_col > first_col Then '單列AutoFill報錯Cells(i, first_col).AutoFill Destination:=Range(Cells(i, first_col), Cells(i, last_col))End IfRange(Cells(i, first_col), Cells(i, last_col)).Value = Range(Cells(i, first_col), Cells(i, last_col)).ValueRange(Cells(i, first_col), Cells(i, last_col)).Interior.Color = vbYellow '清除公式，標記顏色sum_j = i + 1 '更新值避免重復計算End IfNextEnd Sub

舉例

自動按月合計

參照《Excel·VBA選中列一鍵計算小計總計》，對上面的代碼改為自動按月合計，無需事先在每個月份后手動添加“本月合計”行，使用更加方便

Sub 選中列自動每月合計()'適用單/多列選中、單/多列部分選中；上一個sub的自動按月版Dim key_col&, rng As Range, i&, j&, k&Dim first_row&, last_row&, first_col&, last_col&, start_row&, end_row& '--------------------參數填寫：key_col都為數字key_col = 1 '日期列號，對連續相同月份的進行合計；非日期不統計Set rng = Intersect(ActiveSheet.UsedRange, Selection) 'intersect語句避免選擇整列造成無用計算first_row = rng.row '選中區域開始行號last_row = first_row + rng.Rows.Count - 1 '選中區域結束行號first_col = rng.column '選中區域開始列號last_col = first_col + rng.Columns.Count - 1 '選中區域結束列號With ActiveSheetstart_row = first_row: end_row = last_row + 1 'end_row+1方便最后一個區域計算DoIf TypeName(.Cells(start_row, key_col).Value) <> "Date" Thenstart_row = start_row + 1Elsemonth_s = Format(.Cells(start_row, key_col), "yyyy.mm") '年月For j = start_row + 1 To end_rowmonth_t = Format(.Cells(j, key_col), "yyyy.mm")If TypeName(.Cells(j, key_col).Value) <> "Date" Or month_t <> month_s Then '非日期、非同月.Rows(j).Insert.Cells(j, key_col) = month_s & "合計"For k = first_col To last_col.Cells(j, k).FormulaR1C1 = "=SUM(R[-" & j - start_row & "]C:R[-1]C)"Next'也可清除公式僅保留結果Range(.Cells(j, first_col), .Cells(j, last_col)).Value = Range(.Cells(j, first_col), .Cells(j, last_col)).Valuestart_row = j + 1: end_row = end_row + 1 '開始、結束行號更新值Exit For '結束for循環End IfNextEnd IfLoop Until start_row >= end_rowEnd WithDebug.Print "按月合計行插入完成" End Sub

總結

以上是生活随笔為你收集整理的Excel·VBA一键计算每月合计的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。