VBA自动填充公式问题求助：自动运行失效、错误13及无值单元格

问题1：代码放在ThisWorkbook无法自动运行，及性能疑问 #

原因 #

普通Sub过程放在ThisWorkbook中不会自动触发，必须借助工作簿/工作表事件才能实现输入值时自动运行。

解决方案 #

将代码改为Workbook_SheetChange事件（放在ThisWorkbook模块），仅监听A列的修改，只处理当前修改的行，而非全表循环，性能比按钮触发更高效（避免每次全表扫描）：

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim FeuilleExclue As Variant
    Dim Exclure As Boolean
    Dim NomFeuille As Variant
    Dim rowIndex As Long
    
    ' 仅处理A列的单个单元格修改
    If Target.Column <> 1 Or Target.Cells.Count > 1 Then Exit Sub
    rowIndex = Target.Row
    If rowIndex < 2 Then Exit Sub ' 跳过表头行
    
    ' 检查当前工作表是否在排除列表
    FeuilleExclue = Array("HISTO", "VUE FINALE ANALYTIQUE", "BFT RENDEMENT 2030 CLIMAT")
    Exclure = False
    For Each NomFeuille In FeuilleExclue
        If Sh.Name = NomFeuille Then
            Exclure = True
            Exit For
        End If
    Next NomFeuille
    If Exclure Then Exit Sub
    
    ' 禁用事件防止循环触发
    Application.EnableEvents = False
    ' 仅当A列单元格有值时填充公式
    If Not IsEmpty(Sh.Cells(rowIndex, 1).Value) And Not IsError(Sh.Cells(rowIndex, 1).Value) Then
        ' 修正引号转义，简化公式写法
        Sh.Cells(rowIndex, 8).FormulaR1C1 = "=IF(RC5<>"""",RC5/RC3-1,"""")" ' 列H
        Sh.Cells(rowIndex, 9).FormulaR1C1 = "=IFERROR(IF(AND(RC8<>"""",RC13<>""""),RC8-RC13,""""),"""")" ' 列I
        Sh.Cells(rowIndex, 10).FormulaR1C1 = "=IF(RC9<>"""",ABS(RC9),"""")" ' 列J
        Sh.Cells(rowIndex, 11).FormulaR1C1 = "=IF(R[+1]C7<>"""",R[+1]C7,"""")" ' 列K
        Sh.Cells(rowIndex, 12).FormulaR1C1 = "=IF(R[+1]C7<>"""",RC11-RC7,"""")" ' 列L
        Sh.Cells(rowIndex, 13).FormulaR1C1 = "=IFERROR(IF(RC11<>"""",VLOOKUP(RC11,C15:C17,3,FALSE),""""),"""")" ' 列M，增加IFERROR处理查找失败
    Else
        ' 清空A列无值时的公式单元格
        Sh.Cells(rowIndex, 8).Resize(1, 6).ClearContents
    End If
    Application.EnableEvents = True
End Sub

性能说明 #

事件触发模式仅在A列有修改时运行，且只处理当前行，比按钮触发全表循环的效率更高，不会额外拖慢文件。

问题2：运行时错误13（类型不匹配） #

原因 #

1. 未声明变量：原代码中NomFeuille未声明，默认是变体类型，可能导致工作表名称匹配时类型冲突；
2. 错误值判断：ws.Cells(rowIndex, 1).Value <> ""如果A列单元格是错误值（如#N/A、#DIV/0!），会触发类型不匹配；
3. 公式引号转义错误：原代码中部分公式的引号转义错误（如RC13<>\"\"\"\"\"\"多了冗余的转义引号）。

解决方案 #

1. 开头添加Option Explicit强制声明所有变量；
2. 用Not IsError(ws.Cells(rowIndex, 1).Value)和Not IsEmpty(...)替代直接判断<>""；
3. 修正公式中的引号转义：VBA中字符串里的双引号用两个双引号表示（""），无需额外反斜杠转义。

问题3：引用旧值的单元格未生成值 #

原因 #

1. 手动计算模式：原代码中设置Application.Calculation = xlCalculationManual，虽然后面改回自动，但循环中填充的公式可能未及时计算；
2. 公式依赖错误：部分公式（如列M的VLOOKUP）引用的单元格可能是错误值，原公式未处理错误，导致显示空白或错误；
3. 行引用问题：列K、L的公式引用R[+1]C7（下一行的G列），如果当前行是最后一行，R[+1]C7会引用空白单元格，导致公式返回空白。

解决方案 #

1. 事件触发模式下无需设置手动计算，保持自动计算即可；
2. 给依赖外部数据的公式添加IFERROR（如列M的VLOOKUP），处理查找失败的情况；
3. 增加对R[+1]C7的有效性判断，比如：

   Sh.Cells(rowIndex, 11).FormulaR1C1 = "=IF(AND(R[+1]C7<>"""",ROW()<COUNTA(C:C)),R[+1]C7,"""")"
   

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