Excel VBA插入公式并向下填充时运行时错误的代码修改请求

看起来你遇到的核心问题是VBA字符串中转义双引号以及公式引用的上下文适配的问题，另外直接用FillDown时如果起始单元格公式本身就有错误，也会导致填充失败。我帮你整理了修改后的代码，同时解释关键的修改点：

关键问题分析 #

• 双引号转义：在VBA中，字符串里的双引号必须用两个连续的双引号("")来表示原公式中的单个双引号，否则VBA会把原公式里的双引号当成字符串的结束标记，直接触发语法错误。
• 公式的本地适配：如果你的Excel是中文环境，使用FormulaLocal比Formula更稳妥，它会适配本地的公式语法（虽然函数名是英文，但区域设置相关的引用格式会更兼容）。
• 工作表存在性检查：先确认你引用的xy、Config、Report工作表都存在，避免因为工作表名称拼写错误导致对象引用错误。
• 动态获取填充范围：直接从Config!E4读取需要填充的最后行号，避免硬编码行号，更灵活适配你的配置。

修改后的完整代码 #

Sub AddAndFillFormulas()
    Dim targetWs As Worksheet
    Dim lastFillRow As Long
    
    ' 检查目标工作表"xy"是否存在
    On Error Resume Next
    Set targetWs = ThisWorkbook.Sheets("xy")
    On Error GoTo 0
    If targetWs Is Nothing Then
        MsgBox "找不到名为'xy'的工作表，请检查名称拼写！"
        Exit Sub
    End If
    
    ' 获取需要填充的最后行号（来自Config!E4）
    lastFillRow = ThisWorkbook.Sheets("Config").Range("E4").Value
    If lastFillRow < 3 Then
        MsgBox "Config!E4的值小于3，无需执行填充操作！"
        Exit Sub
    End If
    
    ' 设置F3公式并填充到指定行
    targetWs.Range("F3").FormulaLocal = "=IF(ROW()>Config!$E$4,"""",VLOOKUP(ROUNDUP((ROW(F3)-2)/Config!$E$2,0),Config!$G:$H,2,FALSE))"
    targetWs.Range("F3:F" & lastFillRow).FillDown
    
    ' G列公式与F列完全一致，直接批量设置更高效
    targetWs.Range("G3:G" & lastFillRow).FormulaLocal = targetWs.Range("F3").FormulaLocal
    
    ' 设置Q3公式并填充
    targetWs.Range("Q3").FormulaLocal = "=IF(ROW()>Config!$E$4,"""",IF(INDIRECT(""Config!B""&ROW(Config!B2))=0,INDIRECT(""Q""&(ROWS(Config!$B$2:B2)-(Config!$E$2-2))),Config!B2))"
    targetWs.Range("Q3:Q" & lastFillRow).FillDown
    
    ' 设置R3公式并填充
    targetWs.Range("R3").FormulaLocal = "=IF(ROW()>Config!$E$4,"""",IF(HLOOKUP(Q3,Report!$2:$65536,ROUNDUP(((ROW(R3)-2)/Config!$E$2),0)+1,FALSE)=""Passed"",""OK"",""KO""))"
    targetWs.Range("R3:R" & lastFillRow).FillDown
End Sub

额外排查建议 #

• 检查Config工作表的E2、E4单元格是否为有效数值，避免出现除数为0或者无效行号的情况。
• 确认Report工作表的第2行及以下区域有HLOOKUP需要匹配的数据，避免公式返回#N/A错误（虽然这不会导致VBA运行时错误，但会影响最终结果）。
• 如果仍然报错，查看错误提示的具体编号：
  • 错误'9'：下标越界 → 大概率是工作表名称拼写错误
  • 错误'1004'：应用程序定义或对象定义错误 → 公式中的单元格引用有误，或者文件权限问题

内容的提问来源于stack exchange，提问作者Viktória Bernánd