如何在VBA的R1C1公式中使用常量变量？1004报错求解

出错的核心原因 #

1. 未绑定工作表的Cells引用：原代码里Cells(Row, col)没有指定所属的ws工作表，若当前激活的工作表不是ws，会导致ws.Range(...)引用跨表单元格组合，直接触发1004运行时错误。
2. 数值格式与公式语法冲突：如果dollar是带区域格式的数值（比如含逗号作为千分位/小数分隔符），直接拼接进公式会让Excel无法识别，从而显示#NAME?错误。
3. 冗余的Range写法：ws.Range(Cells(Row, col), Cells(Row, col))完全可以简化为ws.Cells(Row, col)，减少不必要的复杂度。

修正后的代码 #

' 1. 给Cells指定所属工作表，避免跨表引用错误
ws.Cells(Row, col).FormulaR1C1 = "=IFERROR(VLOOKUP(R" & cotacao & "C[-1],Cotações!R6C2:R25C3,2,0),""-"")"

' 2. 将dollar转换为纯数值类型，确保公式能正确解析
Dim dolar As Double
dolar = CDbl(ws.Cells(Row, col).Value)

' 3. 用纯数值拼接公式，保持R1C1语法正确
ws.Cells(Row, col).FormulaR1C1 = "=RC[-1]*RC17/RC5*" & Str(dolar)

效率优化方案 #

如果不需要保留中间的VLOOKUP公式，可以直接用VBA完成查找计算，跳过写入公式再读取的步骤，提升代码效率：

' 直接用VBA执行VLOOKUP逻辑
Dim lookupVal As Variant
lookupVal = ws.Cells(cotacao, col - 1).Value
dolar = Application.VLookup(lookupVal, ThisWorkbook.Worksheets("Cotações").Range("R6C2:R25C3"), 2, False)
' 处理查找失败的情况，可根据需求替换默认值
If IsError(dolar) Then dolar = 0 

' 写入最终公式
ws.Cells(Row, col).FormulaR1C1 = "=RC[-1]*RC17/RC5*" & Str(dolar)

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