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 =...
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!