Run-time error '1004': Application-defined or object-defined error

[eoinymc]

Hi,

I am receiving the above error when trying to process this line of code:

FinalRow = 20

Cells(4, 18).Resize(FinalRow - 3, 1).Formula = _
"=IF(ISNA(IF(($P4*(INDEX(Handling!$C$8:$AO$8,MATCH($D4,Handling!$C$4:$AO$4,0))))>(INDEX(Handling!$C$7:$AO$7,MATCH($D4,Handling!$C$4:$AO$4,0))),($P4*(INDEX(Handling!$C$8:$AO$8,MATCH($D4,Handling!$C$4:$AO$4,0)))),(INDEX(Handling!$C$7:$AO$7,MATCH($D4,Handling!$C$4:$AO$4,0))))),"",IF(($P4*(INDEX(Handling!$C$8:$AO$8,MATCH($D4,Handling!$C$4:$AO$4,0))))>(INDEX(Handling!$C$7:$AO$7,MATCH($D4,Handling!$C$4:$AO$4,0))),($P4*(INDEX(Handling!$C$8:$AO$8,MATCH($D4,Handling!$C$4:$AO$4,0)))),(INDEX(Handling!$C$7:$AO$7,MATCH($D4,Handling!$C$4:$AO$4,0)))))"


I know it's a generic error.

It works if I take out the If(isna(...) part of the function...and it also works if I paste the above formula directly into the cell.

Do you think it could be got to do with the fact that I'm trying to populate more than 1 row (FinalRow variable) here?

Any help would be greatly appreciated.

Cheers,

Eoin

 

[Jonmo1]

when you enter a formula in a cell with VBA
AND that formula contains Quotes, you need to double up those quotes.

Try

Cells(4, 18).Resize(FinalRow - 3, 1).Formula = _
"=IF(ISNA(IF(($P4*(INDEX(Handling!$C$8:$AO$8,MATCH($D4,Handling!$C$4:$AO$4,0))))>(INDEX(Handling!$C$7:$AO$7,MATCH($D4,Handling!$C$4:$AO$4,0))),($P4*(INDEX(Handling!$C$8:$AO$8,MATCH($D4,Handling!$C$4:$A O$4,0)))),(INDEX(Handling!$C$7:$AO$7,MATCH($D4,Handling!$C$4:$AO$4,0))))),"""",IF(($P4*(INDEX(Handling!$C$8:$AO$8,MATCH($D4,Handling!$C$4:$AO$4,0))))>(INDEX(Handling!$C$7:$AO$7,MATCH($D4,Handling!$C$4:$AO$4,0))),($P4*(INDEX(Handling!$C$8:$AO$8,MATCH($D4,Handling!$C$4:$A O$4,0)))),(INDEX(Handling!$C$7:$AO$7,MATCH($D4,Handling!$C$4:$AO$4,0)))))"

Hope that helps.