Solved Error 1004 When Passing Formula to Excel (1 Viewer)

[pooldead]

I have the following formula that runs as expected when used directly in Excel. But when I try to add it into my Access VBA, I get error 1004 "Application Defined or Object Defined Error". I think it has to do with my double-quotes to identify text in the formula, but I don't know how to reconcile it.

=IFS(ISNA(VLOOKUP($C2,'A1 Report'!$A:$E,4,FALSE)),"None",VLOOKUP($C2,'A1 Report'!$A:$E,4,FALSE)=0,"None",NOT(ISERR(VLOOKUP($C2,'A1 Report'!$A:$E,4,FALSE))),VLOOKUP($C2,'A1 Report'!$A:$E,4,FALSE))

.Range("J2").Formula = "=IFS(ISNA(VLOOKUP($C2,'A1 Report'!$A:$E,4,FALSE)),"None",VLOOKUP($C2,'A1 Report'!$A:$E,4,FALSE)=0,"None",NOT(ISERR(VLOOKUP($C2,'A1 Report'!$A:$E,4,FALSE))),VLOOKUP($C2,'A1 Report'!$A:$E,4,FALSE))"

 

[pooldead]

This article led me to the solution. I was right, the double quotes were a problem.

https://stackoverflow.com/questions/13123230/writing-formula-into-excel-through-access-vba