Search code examples
excelvbaexcel-formulasumifs

How to apply quotes in formula with multiple criteria?

I need to input a formula into a macro but I get

Application-defined or object-defined error

The formula is

=SUMIFS(FBL5N!O:O,FBL5N!D:D,Y1120_CREDIT!B6,FBL5N!S:S,{"<>0","<>3","<>9","<>O","<>D"})

I have this in the code 

Range("P6").Formula = "=SUMIFS(FBL5N!O:O,FBL5N!D:D,Y1120_CREDIT!B6,FBL5N!S:S,{" & "<>0" & "," & "<>3" & "," & "<>9" & "," & "<>O" & "," & "<>D" & "})"

I have tried putting quotations in different ways and still with the same result.

Solution

  • Just double all your quotes in the string:

    Range("P6").Formula = "=SUMIFS(FBL5N!O:O,FBL5N!D:D,Y1120_CREDIT!B6,FBL5N!S:S,{""<>0"",""<>3"",""<>9"",""<>O"",""<>D""})"