replacing a formula in excel.

[Excelguruintraining]

So i have a VBA macro that clears the contents of cells in my worksheet. I want to replace the formula in a cell just in case the user clears it. the formula i want in the cell is. =IF(E99="NO",(E100*55),"Ask An Estimator",0) the line of code im having a problem with is below. Its giving me an end statement error, with the "No" selected. Any help is appreciated!

 Range("E101") = "=IF(E99="NO",(E100*55),"Ask An Estimator")"

Sub NextInvoice()
Application.ScreenUpdating = False
Worksheets("Quote prep").Select
Range("PrepAmt").ClearContents
Range("PrepDisc").ClearContents
Range("PrepCost").ClearContents
Range("Stalls").MergeArea.ClearContents
Range("Screens").MergeArea.ClearContents
Range("E98").ClearContents
Range("E101").ClearContents
Range("E101") = "=IF(E99="NO",(E100*55),"Ask An Estimator")"










Range("Quote").Value = Range("Quote").Value + 1
Range("CompName").MergeArea.ClearContents
Range("ContName").MergeArea.ClearContents
Range("Phone").MergeArea.ClearContents
Range("Email").MergeArea.ClearContents
Range("CustPo").MergeArea.ClearContents
Range("CompName").MergeArea.ClearContents
Range("ContName").MergeArea.ClearContents
Range("Comments").MergeArea.ClearContents
Range("Terms").MergeArea.ClearContents
Range("LeadTime").MergeArea.ClearContents
Range("Ship").MergeArea.ClearContents
Range("FobPoint").MergeArea.ClearContents
Range("QuoteSpace").ClearContents




Worksheets("Purchase Order").Select
Range("C15").MergeArea.ClearContents
Range("B18").MergeArea.ClearContents
Range("D18").MergeArea.ClearContents
Range("F18").MergeArea.ClearContents
Range("H18").MergeArea.ClearContents
Range("J18").MergeArea.ClearContents
Range("L18").MergeArea.ClearContents
Range("POSpace").ClearContents


Worksheets("Purchase Order (2)").Select
Range("C15").MergeArea.ClearContents
Range("B18").MergeArea.ClearContents
Range("D18").MergeArea.ClearContents
Range("F18").MergeArea.ClearContents
Range("H18").MergeArea.ClearContents
Range("J18").MergeArea.ClearContents
Range("L18").MergeArea.ClearContents
Range("POSpace").ClearContents


Worksheets("Purchase Order (3)").Select
Range("C15").MergeArea.ClearContents
Range("B18").MergeArea.ClearContents
Range("D18").MergeArea.ClearContents
Range("F18").MergeArea.ClearContents
Range("H18").MergeArea.ClearContents
Range("J18").MergeArea.ClearContents
Range("L18").MergeArea.ClearContents
Range("POSpace").ClearContents


Worksheets("Purchase Order (4)").Select
Range("C15").MergeArea.ClearContents
Range("B18").MergeArea.ClearContents
Range("D18").MergeArea.ClearContents
Range("F18").MergeArea.ClearContents
Range("H18").MergeArea.ClearContents
Range("J18").MergeArea.ClearContents
Range("L18").MergeArea.ClearContents
Range("PO4Space").ClearContents


Worksheets("Purchase Order (5)").Select
Range("C15").MergeArea.ClearContents
Range("B18").MergeArea.ClearContents
Range("D18").MergeArea.ClearContents
Range("F18").MergeArea.ClearContents
Range("H18").MergeArea.ClearContents
Range("J18").MergeArea.ClearContents
Range("L18").MergeArea.ClearContents
Range("PO5Space").ClearContents






Application.ScreenUpdating = True






End Sub

 

[Claus8528]

 Range("E101") = "=IF(E99="NO",(E100*55),"Ask An Estimator")"

I think it's

Range("E101").[B]Formula[/B] = "=IF(E99="NO",(E100*55),"Ask An Estimator")"

 

[Rick Rothstein]

I think it's

Range("E101").[B]Formula[/B] = "=IF(E99="[B][COLOR="#FF0000"]"[/COLOR][/B]NO[B][COLOR="#FF0000"]"[/COLOR][/B]",(E100*55),"[B][COLOR="#FF0000"]"[/COLOR][/B]Ask An Estimator[B][COLOR="#FF0000"]"[/COLOR][/B]")"

You also have to double up any internal quote marks as well (see red above).

 

[Excelguruintraining]

Thank you both!!!