Search code examples
vbaruntime-error

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
Solution

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