如何用VBA为每日更新的动态表格列批量分配公式？

优化后的代码 #

Sub SetDynamicFormulas()
    Dim lastRow As Long
    ' 获取C列最后一行数据的行号（假设C列是数据列，行数与B列匹配）
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    
    ' 批量设置B2到B列最后一行的公式
    Range("B2:B" & lastRow).FormulaR1C1 = _
        "=(R12C11 - SUM(R2C3:RC3))/(R13C12 - (ROW()-2))"
End Sub

代码说明 #

1. 自动识别动态行数
   用Cells(Rows.Count, "C").End(xlUp).Row自动定位C列最后一个有数据的行，无需手动指定行数，完美适配每日变化的表格。如果数据行的判断基准不是C列，替换"C"为对应列标即可（比如A列写"A"）。

2. 批量赋值公式
   直接对B2到目标行的整段区域设置公式，避免原代码中逐个Select单元格的冗余操作，运行效率更高，代码更简洁。

3. 公式逻辑匹配

   • R12C11：对应固定单元格K12（GOAL AG），R1C1表示法中，R后为行号，C后为列号（K是第11列）。
   • SUM(R2C3:RC3)：自动计算从C2到当前行C列的总和，适配任意行位置。
   • R13C12：对应固定单元格L13（工作日数）。
   • (ROW()-2)：计算当前行的偏移量，B2时偏移0，B3时偏移1，完全匹配原代码的偏移逻辑。

补充说明 #

如果你的表格是Excel结构化列表（ListObject），可以改用结构化引用版本的公式，比如：

Range("Table1[B列列名]").FormulaR1C1 = _
    "=(Table1[[#Headers],[GOAL AG]] - SUM(Table1[[REAL AG ]]:[@[REAL AG ]]))/(Table1[[#Headers],[工作日数]] - (ROW()-ROW(Table1[#Headers])-1))"

但普通区域版本的代码兼容性更强，适用于大多数场景。

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