Application or Object Defined Error

[Nelly2015]

No idea what went wrong. Need help with the following code. It is simple.


Sub Purchase2020()
Dim FinalRow As Long
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
ActiveWorkbook.Sheets("2020").Range("AJ2").Select
Range("AJ2:AJ" & FinalRow) = "=IFERROR(RC[-21]/RC[-1],"")"
End Sub

 

[KOKOSEK]

Range("AJ2:AJ" & FinalRow).FormulaR1C1 = "=IFERROR(RC[-21]/RC[-1],"""")"

 

[Nelly2015]

Range("AJ2:AJ" & FinalRow).FormulaR1C1 = "=IFERROR(RC[-21]/RC[-1],"""")"

FormulaR1C1 is not the issue. My other codes dont include it. They work.

But I don't why you put """" instead of "". Any reason ?

 

[Peter_SSs]

But I don't why you put """" instead of "". Any reason ?

Yes, the formula is a string enclosed by quote marks. If you use standard quote marks within the string, vba does not know if they are part of the string or whether the first one marks the end of the string so you double them up. Once the formula is in the worksheet you will see that those 4 quote marks revert to two.

Range("AJ2:AJ" & FinalRow) = "=IFERROR(RC[-21]/RC[-1],"""")"

BTW, although not mandatory, using .FormulaR1C1 would make it clearer that your code is inserting a formula in that format.

 

[Nelly2015]

Yes, the formula is a string enclosed by quote marks. If you use standard quote marks within the string, vba does not know if they are part of the string or whether the first one marks the end of the string so you double them up. Once the formula is in the worksheet you will see that those 4 quote marks revert to two.

Range("AJ2:AJ" & FinalRow) = "=IFERROR(RC[-21]/RC[-1],"""")"

BTW, although not mandatory, using .FormulaR1C1 would make it clearer that your code is inserting a formula in that format.

Thanks for your clear and detailed explanations. The code worked after doubling the quotation marks.