VBA设置FormulaR1C1触发应用程序定义或对象定义错误求助

• 核心报错和全局变量无关，是你把VBA循环变量j直接写进了公式字符串里，VBA不会自动把变量值替换到字符串中，导致Excel收到的公式里包含非法的R[j]语法，触发定义错误。
• 还有几个隐性问题也会导致异常：
  • 变量声明不规范：Dim achWS, refWS As Worksheet 中只有refWS是工作表类型，achWS实际是变体类型；行号变量使用Integer容易溢出（上限仅32767），建议改用Long类型
  • Find方法未做空判断，如果AB列没有数据会直接报错
  • 无意义的Select/ActiveCell操作既拉低效率，也容易因活动页变化触发异常

Sub create_jvs()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim achWS As Worksheet, refWS As Worksheet
    Dim j As Long, fRow As Long, lRow As Long
    Dim findRng As Range
    
    Set refWS = wb.Sheets("References")
    Set achWS = wb.Sheets("ACHLinkage")
    
    '获取行范围，加空判断避免报错
    fRow = 2
    Set findRng = achWS.Columns("AB").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues)
    If findRng Is Nothing Then
        MsgBox "AB列未找到有效数据，程序退出"
        Exit Sub
    End If
    lRow = findRng.Row
    
    '循环生成独立的jvs
    For j = -29 To (-29 + (lRow - fRow)) Step 1
        '直接操作单元格，不需要Select
        '拼接变量j到公式中
        refWS.Range("R31").FormulaR1C1 = "=IF(ACHLinkage!R[" & j & "]C[10] = """",  """", IF(ACHLinkage!R[" & j & "]C[10]=""N"",ROUNDDOWN(References!R30C19*ACHLinkage!R[" & j & "]C[11],2),ROUNDUP(References!R30C19*ACHLinkage!R[" & j & "]C[11],2)))"
        refWS.Range("R32").FormulaR1C1 = "=IF(ACHLinkage!R[" & j - 1 & "]C[10] = """", """", IF(ACHLinkage!R[" & j - 1 & "]C[10] = ""N"", ROUNDDOWN(References!R28C19*ACHLinkage!R[" & j - 1 & "]C[11],2), ROUNDUP(References!R28C19*ACHLinkage!R[" & j - 1 & "]C[11],2)))"
        refWS.Range("R33").FormulaR1C1 = "=IF(ACHLinkage!R[" & j - 2 & "]C[10] = """", """", IF(ACHLinkage!R[" & j - 2 & "]C[10]=""N"",ROUNDDOWN(USERFORM!R26C6*ACHLinkage!R[" & j - 2 & "]C[11],2),ROUNDUP(USERFORM!R26C6*ACHLinkage!R[" & j - 2 & "]C[11],2)))"
        refWS.Range("R34").FormulaR1C1 = "=IF(ACHLinkage!R[" & j - 3 & "]C[10] = """", """", IF(ACHLinkage!R[" & j - 3 & "]C[10]=""N"",ROUNDDOWN(USERFORM!R26C7*ACHLinkage!R[" & j - 3 & "]C[11],2),ROUNDUP(USERFORM!R26C7*ACHLinkage!R[" & j - 3 & "]C[11],2)))"
        refWS.Range("R35").FormulaR1C1 = "=IF(ACHLinkage!R[" & j - 4 & "]C[10] = """", """", IF(ACHLinkage!R[" & j - 4 & "]C[10]=""N"",ROUNDDOWN(USERFORM!R26C8*ACHLinkage!R[" & j - 4 & "]C[11],2),ROUNDUP(USERFORM!R26C8*ACHLinkage!R[" & j - 4 & "]C[11],2)))"
        
        ThisWorkbook.Sheets("ACH JV").Copy   '将jvWS复制到新工作簿
    Next j
    
    '重置参数，同样去掉Select操作
    refWS.Range("R31").FormulaR1C1 = "=IF(ACHLinkage!R[-29]C[10] = """",  """", IF(ACHLinkage!R[-29]C[10]=""N"",ROUNDDOWN(References!R30C19*ACHLinkage!R[-29]C[11],2),ROUNDUP(References!R30C19*ACHLinkage!R[-29]C[11],2)))"
    refWS.Range("R32").FormulaR1C1 = "=IF(ACHLinkage!R[-30]C[10] = """", """", IF(ACHLinkage!R[-30]C[10] = ""N"", ROUNDDOWN(References!R28C19*ACHLinkage!R[-30]C[11],2), ROUNDUP(References!R28C19*ACHLinkage!R[-30]C[11],2)))"
    refWS.Range("R33").FormulaR1C1 = "=IF(ACHLinkage!R[-31]C[10] = """", """", IF(ACHLinkage!R[-31]C[10]=""N"",ROUNDDOWN(USERFORM!R26C6*ACHLinkage!R[-31]C[11],2),ROUNDUP(USERFORM!R26C6*ACHLinkage!R[-31]C[11],2)))"
    refWS.Range("R34").FormulaR1C1 = "=IF(ACHLinkage!R[-32]C[10] = """", """", IF(ACHLinkage!R[-32]C[10]=""N"",ROUNDDOWN(USERFORM!R26C7*ACHLinkage!R[-32]C[11],2),ROUNDUP(USERFORM!R26C7*ACHLinkage!R[-32]C[11],2)))"
    refWS.Range("R35").FormulaR1C1 = "=IF(ACHLinkage!R[-33]C[10] = """", """", IF(ACHLinkage!R[-33]C[10]=""N"",ROUNDDOWN(USERFORM!R26C8*ACHLinkage!R[-33]C[11],2),ROUNDUP(USERFORM!R26C8*ACHLinkage!R[-33]C[11],2)))"
End Sub

• 公式拼接时，VBA变量需要用&和字符串部分连接，才能把变量的实际值传入公式
• 去掉所有Select操作后，程序运行速度会明显提升，也不会因为用户手动切换工作表导致异常

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