ERROR in Updating cells with formulla in a range VBA

[mahhdy]

Hey Bros,
I am facing an error with this simple code.
It can't write the formula in the cells.
Can you please help.
Thanks

Sub update_formula()

    Dim c As Range
    With Materials
        For Each c In .Range("tbl[Shortage]")
'        Debug.Print c.Address
'        Debug.Print Application.WorksheetFunction.IsFormula(c)
        If Application.WorksheetFunction.IsFormula(c) Then c.Formula = _
        "=IFERROR(IF([@MTYPE]=""Book"",IF(OR(VLOOKUP([@[Work  Book]],Ac_WB2[[VWI]:[RCVD '#]],10,0)="""",VLOOKUP([@[Work  Book]],Ac_WB2[[VWI]:[RCVD  '#]],10,0)=""Short""),""YES"",""NO""),IF(OR([@MTYPE]=""Consumable"",[@MTYPE]=""Chemical"",[@QTY]<1,LEFT([@Material],5)=""Dummy""),""NO"",IF([@Vendor]=""WESCO"",IF([@[HDWR  Loc]]<>""_N/A"",IF([@Location]="""",""N/T"",IF(LEFT([@Locatio"  & _
        "FK"",""NO"", IF([@[RCV File  QTY]]="""",""NO"",""YES""))),IF([@Location]="""",""YES"",IF(LEFT([@Location],2)=""FK"",""NO"",  IF([@[RCV File  QTY]]="""",""NO"",""YES"")))),IF([@Location]<>"""",IF(LEFT([@Location],2)=""FK"",""NO"",  IF([@[RCV File QTY]]="""",""NO"",""YES"")),IF(NOT(AND([@[Rcvd  Date]]="""",[@[R/N]]="""")),""ATTN"",""YES""))))),"""")"
        Next
    End With
End Sub

I tried c.Formular1c1 = too.
Yours,
M

 

[amdopt]

The first part of your formula where you close the quote (underlined below) then you open another quote with nothing in between will throw an error. I understand that you have the word "Book" as part of your formula but this will throw an error. Use numbers in formulas and have a separate legend that interprets the formula output's corresponding numbers.

That part of your formula has a ton of errors in it. It is really annoying to debug that too because of all the words in quotes in the formula. Good luck!
"=IFERROR(IF([@MTYPE]=""Book""

 

[mahhdy]

thanks,
You point is right. I tried using formula generated by macro too, but even it is not working. I modified the formula in MS word and replaced all " with "", so now it is working.
but the funny part is I forgot to disable events, made calculation manual and etc so, it is ****ing slow code...

Thanks again
Sincerely,
M