Excel宏动态插入静态公式引用问题：避免公式随新表更新

核心问题分析 #

1. 行号计算错误：代码中r = Sheets("Overview").Cells(Sheet1.Rows.count, 1).End(xlUp).Row使用了Sheet1.Rows.count，但Sheet1不一定指向"Overview"工作表，导致每次获取的最后一行行号错误，新公式直接覆盖旧行内容，看起来像是旧公式被更新。
2. 公式赋值方式不统一：部分单元格用.Value赋值公式字符串，Excel可能无法正确识别为公式，或引发解析异常。
3. 存在不完整的公式引用：比如"='" & nom & "'!$A$"缺少行号，导致公式无效，可能引发连锁问题。

具体修改步骤 #

1. 修正最后一行行号计算
   将行号计算代码改为：

   r = Sheets("Overview").Cells(Sheets("Overview").Rows.Count, 1).End(xlUp).Row
   

   确保基于"Overview"工作表自身的行数计算最后一行，避免引用错误的工作表。

2. 统一使用.Formula属性设置公式
   所有需要写入公式的单元格，改用.Formula替代.Value，让Excel正确解析公式字符串。例如：

   Sheets("Overview").Cells(r, 1).Formula = "='" & nom & "'!$D$1"
   Sheets("Overview").Cells(r, 2).Formula = "='" & nom & "'!$E$1"
   Sheets("Overview").Cells(r, 3).Formula = "='" & nom & "'!$C$4"
   Sheets("Overview").Cells(r, 4).Formula = "='" & nom & "'!$E$4"
   

3. 补全不完整的公式引用
   代码中Cells(r,5)、Cells(r,6)、Cells(r,7)的公式缺少行号，需补充完整（根据实际需求调整行号）：

   Sheets("Overview").Cells(r, 5).Formula = "='" & nom & "'!$A$1"
   Sheets("Overview").Cells(r, 6).Formula = "='" & nom & "'!$B$1"
   Sheets("Overview").Cells(r, 7).Formula = "='" & nom & "'!$D$1"
   

修改后的关键代码段 #

'添加至概览表
'在概览表添加行
Worksheets("Overview").ListObjects("OverviewTable").ListRows.Add
'修正行号计算
r = Sheets("Overview").Cells(Sheets("Overview").Rows.Count, 1).End(xlUp).Row

'添加关联值（统一使用.Formula，补全引用）
Sheets("Overview").Cells(r, 1).Formula = "='" & nom & "'!$D$1"
Sheets("Overview").Cells(r, 2).Formula = "='" & nom & "'!$E$1"
Sheets("Overview").Cells(r, 3).Formula = "='" & nom & "'!$C$4"
Sheets("Overview").Cells(r, 4).Formula = "='" & nom & "'!$E$4"
Sheets("Overview").Cells(r, 5).Formula = "='" & nom & "'!$A$1"
Sheets("Overview").Cells(r, 6).Formula = "='" & nom & "'!$B$1"
Sheets("Overview").Cells(r, 7).Formula = "='" & nom & "'!$D$1"

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