基于日期动态修改VBA中多公式的列引用

问题分析 #

你通过宏录制器生成的代码，想要根据输入的会计期间月末日期提取月份数值Mon_Num，将公式里的固定列引用C[3]替换为动态计算的C[Mon_Num-4]，但直接修改公式时持续报错，核心原因是没有正确将变量值嵌入公式字符串中。

修改后的完整代码 #

Dim Prior_Month As Variant
Dim Mon_Num As Integer
Dim colOffset As Integer

' 获取日期输入，限制仅能输入日期/数字类型，同时处理取消输入的情况
Prior_Month = Application.InputBox("请输入会计期间的月末日期", "期间结束日", "输入日期", Type:=1)
If Prior_Month = False Then Exit Sub

Mon_Num = Month(Prior_Month)
colOffset = Mon_Num - 4 ' 计算动态列偏移量

' 批量给F6:F7单元格赋值公式，无需选中单元格，代码更高效简洁
Range("F6:F7").FormulaR1C1 = "=+FORECAST!R[79]C[" & colOffset & "]/1000"

关键修改说明 #

1. 日期输入优化：将Prior_Month类型改为Variant，添加Type:=1限制输入为日期/数字，同时增加取消输入的判断，避免用户取消操作导致报错。
2. 动态偏移计算：单独计算colOffset = Mon_Num -4，让逻辑更清晰易懂。
3. 公式字符串拼接：通过"C[" & colOffset & "]"将变量值嵌入公式字符串——VBA不会自动解析字符串内的变量，必须手动完成拼接才能让公式识别动态偏移量。
4. 批量赋值优化：直接给目标单元格区域赋值公式，摒弃宏录制器生成的Select和ActiveCell操作，代码更简洁高效。

扩展到多个公式 #

如果需要修改更多公式，重复类似的拼接逻辑即可，示例如下：

' 修改G8单元格公式，列偏移为Mon_Num-2
Range("G8").FormulaR1C1 = "=+FORECAST!R[50]C[" & (Mon_Num - 2) & "]/1000"

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