为何ListObject自动填充绝对引用公式而非按循环代码生成？

问题背景 #

需要给Excel命名表格（ListObject）的Q:AB列填充公式，规则如下：

• D列为固定价格列，公式中需保持列绝对、行相对（如$D2）
• E:P列为用户输入的月度数量，公式中需保持行列全相对（如E2）
• Q:AB列对应计算「价格×当月数量」，支持用户修改E:P值后动态更新
• 需适配表格新增行（每次新增10行），列数固定

当前代码生成的公式全为绝对引用$D$2*$E$2，且仅处理单行，无法满足需求。

错误原因 #

1. Cells.Address默认返回绝对引用（带$），未指定引用类型参数
2. 代码仅循环列，未遍历表格的每一行，只处理了lrow2+1单行
3. 部分Range/Cells未限定工作表（Range("D" & lrow2).Column缺.），易引发跨表错误

修正后的代码（推荐用ListObject原生操作） #

Sub FillTableFormulas()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim tblRow As ListRow
    Dim priceCol As ListColumn
    Dim qtyColStart As Integer, formulaColStart As Integer
    Dim c As Integer
    
    ' 指定目标工作表和表格（替换成你的实际名称）
    Set ws = ThisWorkbook.Worksheets(i) ' i为已定义的工作表索引
    Set tbl = ws.ListObjects("你的表格名称") ' 替换为表格的实际名称
    
    ' 通过表头名称绑定对应列（比硬编码列号更鲁棒）
    Set priceCol = tbl.ListColumns("价格") ' 替换为D列的表头文字
    qtyColStart = tbl.ListColumns("1月").Index ' 替换为E列的表头文字
    formulaColStart = tbl.ListColumns("1月总成本").Index ' 替换为Q列的表头文字
    
    ' 遍历表格所有数据行
    For Each tblRow In tbl.ListRows
        ' 循环12个月的列（E到P共12列）
        For c = 0 To 11
            ' 生成混合引用公式：价格列锁列不锁行，数量行列都不锁
            tblRow.Range(formulaColStart + c).Formula = _
                "=" & priceCol.DataBodyRange(tblRow.Index).Address(RowAbsolute:=False, ColumnAbsolute:=True) & _
                "*" & tblRow.Range(qtyColStart + c).Address(RowAbsolute:=False, ColumnAbsolute:=False)
        Next c
    Next tblRow
End Sub

关键优化点 #

• 用ListObject操作表格：自动适配新增行，无需手动计算最后一行，避免行号错误
• 精准控制引用类型：通过Address参数生成$D2这类混合引用，满足动态更新需求
• 遍历所有数据行：用ListRows循环处理每一行，确保公式批量生成
• 表头绑定列：通过表头名称定位列位置，表格列顺序调整后代码无需修改

简化版（固定列位置时使用） #

如果表格列位置固定（D=4、E=5、Q=17），可以用更简洁的写法：

Sub FillFixedColumnFormulas()
    Dim ws As Worksheet
    Dim dataRowCount As Integer
    Dim r As Integer, c As Integer
    
    Set ws = ThisWorkbook.Worksheets(i)
    dataRowCount = ws.ListObjects(1).DataBodyRange.Rows.Count ' 获取表格数据总行数
    
    ' 遍历每一行（表格表头在第1行，数据从第2行开始）
    For r = 2 To dataRowCount + 1
        ' 循环Q到AB列（列号17到28）
        For c = 17 To 28
            ' 对应数量列是当前公式列减12（Q对应E=17-12=5）
            ws.Cells(r, c).Formula = _
                "=" & ws.Cells(r, 4).Address(RowAbsolute:=False, ColumnAbsolute:=True) & _
                "*" & ws.Cells(r, c - 12).Address(RowAbsolute:=False, ColumnAbsolute:=False)
        Next c
    Next r
End Sub

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