如何利用VBA实现表格中公式跨31列滚动填充？

Hey there! I totally get it—manually dragging those expanding SUM formulas across 31 columns is such a tedious waste of time. Let's fix that with a simple VBA macro that does all the work for you in seconds.

The VBA Code #

Here's a tailored script that generates exactly the formulas you need:

Sub RollingSumFill()
    Dim startCol As Integer
    Dim endCol As Integer
    Dim currentCol As Integer
    Dim targetRow As Integer
    
    ' Set the row where you want the formulas (change to your target row if needed)
    targetRow = 1
    
    ' Start placing formulas in column C (column number 3) and cover 31 total columns
    startCol = 3
    endCol = startCol + 30 ' 31 columns total (startCol + 30 = 33, which is column AG)
    
    For currentCol = startCol To endCol
        ' Build the SUM formula: starts at A{targetRow}, ends at the column left of current cell
        Cells(targetRow, currentCol).Formula = "=SUM(A" & targetRow & ":" & Split(Cells(targetRow, currentCol - 1).Address, "$")(1) & targetRow & ")"
    Next currentCol
End Sub

How It Works #

• Target Row: The targetRow = 1 line sets the row for your formulas. If you need formulas on row 5 instead, just change that number.
• Formula Range: We start in column C (since that's where your first formula =SUM(A1:B1) would go) and fill 31 columns to the right. This means:
  • Cell C1 gets =SUM(A1:B1)
  • Cell D1 gets =SUM(A1:C1)
  • ...
  • Cell AG1 gets =SUM(A1:AF1)
• Dynamic Range: The macro automatically references the column immediately left of each formula cell, so each SUM range expands by one column as we move right—exactly what you asked for.

How to Use This Macro #

1. Open your Excel workbook.
2. Press Alt + F11 to open the VBA Editor.
3. In the left-hand Project Explorer, right-click your workbook name > Insert > Module.
4. Paste the code into the new module window.
5. Press F5 to run the macro, or go back to Excel, open the Developer tab, click Macros, select RollingSumFill, and hit Run.

Optional Adjustments #

• Start in a Different Column: If you want to begin formulas in column E instead of C, change startCol = 5.
• Adjust Number of Columns: Need more than 31 columns? Update endCol = startCol + [number of columns - 1]. For example, 40 columns would be startCol + 39.

内容的提问来源于stack exchange，提问作者Matthew Lee