Excel 2019与365的Formula2R1C1兼容性问题求助

问题根源 #

• Formula2R1C1是Microsoft 365专属属性，用于支持动态数组公式，Excel 2019不支持该属性，因此触发"error 438"。
• 替换为FormulaR1C1后，原公式中的数组拼接运算（如ENG!C[-6]&"|"&ENG!C[-5]）无法被Excel 2019识别为数组操作，导致MATCH函数无法找到匹配值，最终返回0。

解决方案 #

方法1：使用跨版本兼容的FormulaArray属性 #

Excel 2019支持数组公式，通过FormulaArray设置R1C1格式的数组公式，该属性同时兼容Microsoft 365，可实现跨版本运行。

修改后的完整代码：

Sub Macro1()
    Dim ws As Worksheet
    Dim LastRow As Long
    
    ' 直接引用工作表对象，避免低效的Select操作
    Set ws = ThisWorkbook.Sheets("xxx")
    
    With ws
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        ' 用FormulaArray设置数组公式，兼容365和2019
        .Range("G2").FormulaArray = _
        "=IFERROR(IFERROR(INDEX(ENG!C[-4],MATCH(RC[-6]&""|""&RC[-5],ENG!C[-6]&""|""&ENG!C[-5],0)),INDEX(ENG!C[-4],MATCH(RC[-6]&""*"",ENG!C[-6]&""|""&""DEFAULT"",0))),0)"
        
        ' H、I列公式保持原逻辑，直接用FormulaR1C1
        .Range("H2").FormulaR1C1 = "=RC[-4]*RC[-1]"
        .Range("I2").FormulaR1C1 = "=RC[-5]+RC[-1]"
        
        ' 自动填充公式，无需选中单元格
        .Range("G2:I2").AutoFill Destination:=.Range("G2:I" & LastRow), Type:=xlFillDefault
    End With
End Sub

方法2：添加版本判断（可选优化） #

如果需要针对不同版本做更精准适配，可以通过判断Excel版本，分别调用对应属性：

Sub Macro1()
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim is365 As Boolean
    
    Set ws = ThisWorkbook.Sheets("xxx")
    ' 检查是否支持Formula2属性，判断是否为365
    On Error Resume Next
    is365 = Not IsEmpty(ws.Range("A1").Formula2)
    On Error GoTo 0
    
    With ws
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        ' 根据版本选择公式属性
        If is365 Then
            .Range("G2").Formula2R1C1 = _
            "=IFERROR(IFERROR(INDEX(ENG!C[-4],MATCH(RC[-6]&""|""&RC[-5],ENG!C[-6]&""|""&ENG!C[-5],0)),INDEX(ENG!C[-4],MATCH(RC[-6]&""*"",ENG!C[-6]&""|""&""DEFAULT"",0))),0)"
        Else
            .Range("G2").FormulaArray = _
            "=IFERROR(IFERROR(INDEX(ENG!C[-4],MATCH(RC[-6]&""|""&RC[-5],ENG!C[-6]&""|""&ENG!C[-5],0)),INDEX(ENG!C[-4],MATCH(RC[-6]&""*"",ENG!C[-6]&""|""&""DEFAULT"",0))),0)"
        End If
        
        .Range("H2").FormulaR1C1 = "=RC[-4]*RC[-1]"
        .Range("I2").FormulaR1C1 = "=RC[-5]+RC[-1]"
        
        .Range("G2:I2").AutoFill Destination:=.Range("G2:I" & LastRow), Type:=xlFillDefault
    End With
End Sub

关键说明 #

• 移除了所有Select操作，直接通过工作表对象引用单元格，提升代码稳定性与执行效率。
• FormulaArray会自动将公式以数组形式输入，Excel 2019可正确解析公式中的数组拼接逻辑，确保MATCH函数正常匹配。
• 版本判断方法通过检查Formula2属性是否存在，比单纯判断版本号更精准，避免因版本号重叠导致的误判。

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