VBA批量写入INDEX+MATCH公式自动加@致#N/D错误求助

核心原因 #

你遇到的@符号是Excel 365/2021的隐式交集运算符，当用Range.Formula写入数组公式时，Excel会默认按动态数组规则处理，自动添加@把数组运算转为单值运算，导致匹配逻辑失效，出现#N/D错误。

解决方案：使用FormulaArray替代Formula #

要写入传统数组公式（即手动按Ctrl+Shift+Enter的那种），需要用Range.FormulaArray属性，它会直接将公式作为数组公式写入，不会添加@符号。

修改后的完整代码 #

Sub FastMatchIndexx()
    Dim wsClientes As Worksheet
    Dim wsFormatoFazt As Worksheet
    Dim lastRowG As Long, lastRowC As Long, lastRowD As Long, lastRowE As Long, lastRowF As Long
    Dim formulaPart As String

    Set wsClientes = ThisWorkbook.Sheets("Clientes")
    Set wsFormatoFazt = ThisWorkbook.Sheets("FormatoFazt")

    lastRowG = wsClientes.Cells(wsClientes.Rows.Count, "G").End(xlUp).Row
    lastRowC = wsClientes.Cells(wsClientes.Rows.Count, "C").End(xlUp).Row
    lastRowD = wsClientes.Cells(wsClientes.Rows.Count, "D").End(xlUp).Row
    lastRowE = wsClientes.Cells(wsClientes.Rows.Count, "E").End(xlUp).Row
    lastRowF = wsClientes.Cells(wsClientes.Rows.Count, "F").End(xlUp).Row

    Dim lastRowFormatoFazt As Long
    lastRowFormatoFazt = wsFormatoFazt.Cells(wsFormatoFazt.Rows.Count, "A").End(xlUp).Row

    ' 构建数组公式（表名无空格时可省略单引号）
    formulaPart = "=INDEX(Clientes!$G$2:$G$" & lastRowG & ", MATCH(1, (FormatoFazt!A2=Clientes!$C$2:$C$" & lastRowC & ")*(FormatoFazt!F2=Clientes!$D$2:$D$" & lastRowD & ")*(FormatoFazt!T2>=Clientes!$E$2:$E$" & lastRowE & ")*(FormatoFazt!T2<=Clientes!$F$2:$F$" & lastRowF & "), 0))"

    ' 使用FormulaArray写入数组公式
    wsFormatoFazt.Range("U2:U" & lastRowFormatoFazt).FormulaArray = formulaPart
End Sub

为什么之前的方法无效？ #

• 方案1用Range.Formula：Excel会将数组公式自动转换为动态数组的单值运算，添加@符号破坏了原有的数组匹配逻辑。
• 方案2替换&为=：先写入文本再替换的方式，Excel不会自动将文本识别为公式，所以只会显示文本内容，无法计算。

额外优化建议 #

如果Clientes表的C/D/E/F列最后一行是同一行，可以简化lastRow的获取，减少冗余代码：

Dim lastRowClientes As Long
lastRowClientes = wsClientes.Cells(wsClientes.Rows.Count, "C").End(xlUp).Row
' 后续直接用lastRowClientes代替lastRowC/D/E/F

内容的提问来源于stack exchange，提问作者Francisco Augusto Varela Aguir