VBA创建月度循环计数表时多表格公式引用错误求助

嗨Aisha，刚学VBA碰到这种公式引用的坑太正常了，我来帮你捋清楚问题出在哪，以及怎么解决~

问题根源 #

你现在写公式用的是A1格式的固定单元格地址（比如"=H3*G3"），当你通过Offset(SPACE, 0)把这个公式放到下方的表格（比如偏移65行到I68）时，Excel并不会自动把公式里的H3、G3改成H68、G68——因为你写的是固定的单元格坐标，不是相对当前单元格的引用。这就是为什么所有表格的公式都指向第一个表格的原因。

解决方案：改用R1C1相对引用格式 #

VBA里批量生成公式时，R1C1引用格式是绝佳选择，它是基于当前单元格的相对位置来引用的，不管公式放到哪一行哪一列，都会自动适配。举个例子：

• 原来的=H3*G3（I3的公式），用R1C1写就是=RC[-1]*RC[-2]——RC[-1]表示当前单元格左边第1列的同一行，RC[-2]是左边第2列的同一行，这样不管公式放到I3还是I68，都会自动引用当前行的前两列单元格。

下面是你代码里公式部分的修改版，直接替换原来的公式代码就行：

' 改用R1C1格式写公式，自动适配偏移后的位置
ws.Range("I3:I50").Offset(SPACE, 0).FormulaR1C1 = "=RC[-1]*RC[-2]"
ws.Range("L3:L50").Offset(SPACE, 0).FormulaR1C1 = "=IF(ABS(RC[3])>=10,""NEEDS RECOUNT"",""N/A"")"
ws.Range("N3:N50").Offset(SPACE, 0).FormulaR1C1 = "=RC[-9]-RC[-10]"
ws.Range("O3:O50").Offset(SPACE, 0).FormulaR1C1 = "=RC[-9]-RC[-11]"
ws.Range("P3:P50").Offset(SPACE, 0).FormulaR1C1 = "=RC[-10]/RC[-12]"
ws.Range("R3:R50").Offset(SPACE, 0).FormulaR1C1 = "=ABS(RC[-3])"
ws.Range("S3:S50").Offset(SPACE, 0).FormulaR1C1 = "=ABS(RC[-2])"

' 合计部分的公式也改成R1C1格式
ws.Range("A54").Offset(SPACE, 0).FormulaR1C1 = "=SUM(R[-51]C[8]:R[-4]C[8])"
ws.Range("A56").Offset(SPACE, 0).FormulaR1C1 = "=SUM(R[-53]C[17]:R[-6]C[17])"
ws.Range("A58").Offset(SPACE, 0).FormulaR1C1 = "=SUM(R[-55]C[5]:R[-8]C[5])"
ws.Range("A60").Offset(SPACE, 0).FormulaR1C1 = "=SUM(R[-57]C[18]:R[-10]C[18])"
ws.Range("A62").Offset(SPACE, 0).FormulaR1C1 = "=COUNTA(UNIQUE(FILTER(R[-59]C:R[-12]C,R[-59]C:R[-12]C<>"""")))"

额外优化建议：避免使用Activate #

你代码里用了Worksheets(WEEKOF).Activate来切换工作表，这种方式很容易因为手动切换工作表导致代码出错。建议直接定义工作表对象，让代码更稳定：

' 把原来的Worksheets(WEEKOF).Activate替换成：
Set ws = Worksheets(WEEKOF)
' 后续所有Range前面都加上ws.，比如：
ws.Range("A1").Offset(SPACE, 0).Value = DATEVALUE
ws.Range("A52").Offset(SPACE, 0).Value = "Totals"
' ...其他所有Range操作同理

这样修改后，每个表格的公式都会自动引用当前行的对应单元格，不会再指向第一个表格啦~

备注：内容来源于stack exchange，提问作者Aisha Stayer