如何在VBA循环中为可变单元格数量设置SUM求和公式

假设你的数据结构满足：

• 每行对应一个供应商
• 每个供应商的qtdMeses（配额数量列数）存储在该行的指定单元格（比如K列，可按需修改）
• 配额数值从N列开始，连续占据qtdMeses列

以下是循环插入动态SUM公式的具体代码：

Sub InsertDynamicSumFormulas()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim currentRow As Long
    Dim qtdMeses As Integer
    Dim startCol As Integer
    Dim endCol As Integer
    Dim sumFormula As String
    
    ' 指定目标工作表，替换为你的实际表名
    Set ws = ThisWorkbook.Worksheets("成本预估表")
    
    ' 获取数据最后一行（假设A列有供应商标识，可根据实际列调整）
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 配额数值的起始列（N列）
    startCol = ws.Columns("N").Column
    
    ' 从第2行开始循环（第1行为表头）
    For currentRow = 2 To lastRow
        ' 获取当前供应商的配额列数qtdMeses
        qtdMeses = ws.Cells(currentRow, "K").Value ' 替换为qtdMeses所在的列
        
        ' 计算SUM范围的结束列
        endCol = startCol + qtdMeses - 1
        
        ' 用R1C1格式构建动态公式，适配任意行的相对列位置
        sumFormula = "=SUM(RC[" & (startCol - ws.Columns("M").Column) & "]:RC[" & (endCol - ws.Columns("M").Column) & "])"
        
        ' 为M列当前行设置公式
        ws.Cells(currentRow, "M").FormulaR1C1 = sumFormula
    Next currentRow
End Sub

关键细节说明 #

• 采用FormulaR1C1格式无需硬编码列名，能自动适配不同行的相对位置，更灵活
• 如果qtdMeses是VBA循环中的变量（而非单元格存储值），直接替换qtdMeses = ws.Cells(...)为你的变量赋值逻辑即可
• 若要实现用户修改配额数值后自动重新计算，可在工作表的Worksheet_Change事件中触发上述过程：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 当N列及之后的配额数值列被修改时，重新计算M列总和
    If Not Intersect(Target, ws.Columns("N").Resize(, 24)) Is Nothing Then ' 假设最多24个配额列，可调整
        InsertDynamicSumFormulas
    End If
End Sub

备选A1格式构建方式 #

如果更习惯A1格式的公式，可替换公式构建部分为：

sumFormula = "=SUM(" & ws.Cells(currentRow, startCol).Address(False, False) & ":" & ws.Cells(currentRow, endCol).Address(False, False) & ")"
ws.Cells(currentRow, "M").Formula = sumFormula

内容的提问来源于stack exchange，提问作者Matheus Neri