Error when using "ActiveCell.Formula2R1C1" in excel version2019

I'm using microsoft365 while my colleague using excel version2019. There is a issue where when I'm using ActiveCell.Formula2R1C1

ActiveCell.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)"

I will get the desired answer. but however my colleague having error with Formula2R1C1 - error 438 - object doesnt support this property or method - so we tried to change it to FormulaR1C1 and now the calculation return "0" instead of the real answer. Does anyone know how to fixed this? Or how can I fixed this compatibility issue?

ActiveCell.FormulaR1C1 = "=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)"

This is snippet from my code:

Sub Macro1() Sheets("xxx").Select Dim LastRow As Long With Sheets("xxx") LastRow = .Range("A" & .Rows.Count).End(xlUp).Row End With Range("G2").Select ActiveCell.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)" Range("H2").Select ActiveCell.FormulaR1C1 = "=RC[-4]*RC[-1]" Range("I2").Select ActiveCell.FormulaR1C1 = "=RC[-5]+RC[-1]" Range("G2:I2").Select Selection.AutoFill Destination:=Range("G2:I" & LastRow), Type:=xlFillDefault Range("G2:I" & LastRow).Select
End Sub

1 Answer

I tried ActiveCell.FormulaArray now it's worked for both of us!