Application-defined error when adding a formula to a cell in VBA

Question

I am trying to add an IF formula to a cell in excel using a macro and it is displaying a 1004 application-defined or object-defined error for the .Range line. How can I prevent this error?

Sub AddFormula()
Dim Formula As Variant
 With ThisWorkbook.Worksheets("Form Responses 1")
    .Range("CE2").Formula = "=IFERROR(IF(OR((BZ2=""No"", BZ2=""0"")), BZ2, AN2), AN2)"

 End With
End Sub

Thanks

the issue is that your formula is not a valid formula - specifically the OR bit — JimmyShoe
Thank you perfect, it was the bracket after the OR that was the problem — isaaacs

JimmyShoe JimmyShoe · Accepted Answer · 2019-08-14T07:50:13

I think the issue is that the formula you supplied is not valid. There are too many brackets in the OR part.

try this instead

Sub AddFormula()
Dim Formula As Variant
 With ThisWorkbook.Worksheets("Form Responses 1")
.Range("CE2").Formula = "=IFERROR(IF(OR(BZ2=""No"", BZ2=""0""), BZ2, AN2), AN2)"

 End With
End Sub

Application-defined error when adding a formula to a cell in VBA

1 Answers