Type Mismatch error

[gtd526]

Hello,
Im receiving a Type Mismatch error on the enter formula line.
How can this be resolved?
The formula works fine when entered manually.

    Dim c As Range
 
    For Each c In Range("U27:AF27")
        If c.Value < Date Then
            c.Offset(1, 0).Value = _
            "=IF(SUMIFS($E$3:$E$30,$B$3:$B$30," >= "&U$27,$B$3:$B$30," < "&EDATE(U$27,1))+SUMIFS($K$3:$K$30,$H$3:$H$30," >= "&U$27,$H$3:$H$30," < "&EDATE(U$27,1))+SUMIFS($P$3:$P$30,$N$3:$N$30," >= "&U$27,$N$3:$N$30," < "&EDATE(U$27,1))=0,"",SUMIFS($E$3:$E$30,$B$3:$B$30," >= "&U$27,$B$3:$B$30," < "&EDATE(U$27,1))+SUMIFS($K$3:$K$30,$H$3:$H$30," >= "&U$27,$H$3:$H$30," < "&EDATE(U$27,1))+SUMIFS($P$3:$P$30,$N$3:$N$30," >= "&U$27,$N$3:$N$30," < "&EDATE(U$27,1)))"
        End If
    Next

Thank you

 

[Edgar_]

You have to escape the inner double quotes with an additional double quote. Leave the outer double quotes there.

"=IF(SUMIFS($E$3:$E$30,$B$3:$B$30,"" >= ""&U$27,$B$3:$B$30,"" < ""&EDATE(U$27,1))+SUMIFS($K$3:$K$30,$H$3:$H$30,"" >= ""&U$27,$H$3:$H$30,"" < ""&EDATE(U$27,1))+SUMIFS($P$3:$P$30,$N$3:$N$30,"" >= ""&U$27,$N$3:$N$30,"" < ""&EDATE(U$27,1))=0,"""",SUMIFS($E$3:$E$30,$B$3:$B$30,"" >= ""&U$27,$B$3:$B$30,"" < ""&EDATE(U$27,1))+SUMIFS($K$3:$K$30,$H$3:$H$30,"" >= ""&U$27,$H$3:$H$30,"" < ""&EDATE(U$27,1))+SUMIFS($P$3:$P$30,$N$3:$N$30,"" >= ""&U$27,$N$3:$N$30,"" < ""&EDATE(U$27,1)))"

 

[gtd526]

You have to escape the inner double quotes with an additional double quote. Leave the outer double quotes there.

"=IF(SUMIFS($E$3:$E$30,$B$3:$B$30,"" >= ""&U$27,$B$3:$B$30,"" < ""&EDATE(U$27,1))+SUMIFS($K$3:$K$30,$H$3:$H$30,"" >= ""&U$27,$H$3:$H$30,"" < ""&EDATE(U$27,1))+SUMIFS($P$3:$P$30,$N$3:$N$30,"" >= ""&U$27,$N$3:$N$30,"" < ""&EDATE(U$27,1))=0,"""",SUMIFS($E$3:$E$30,$B$3:$B$30,"" >= ""&U$27,$B$3:$B$30,"" < ""&EDATE(U$27,1))+SUMIFS($K$3:$K$30,$H$3:$H$30,"" >= ""&U$27,$H$3:$H$30,"" < ""&EDATE(U$27,1))+SUMIFS($P$3:$P$30,$N$3:$N$30,"" >= ""&U$27,$N$3:$N$30,"" < ""&EDATE(U$27,1)))"

Thank you. I put the double-quotes only to the outside quotes (first and last), not the inner quotes.