help building formula string

[desertsp]

Can anyone explain my error? I have a selection of cells having formulas that sometimes return a zero. I want to update those formulas to conditionally return blanks rather than zeroes. I am receiving a Run-time error '1004' Application-defined or object-defined error when the macro execution reaches cel.Formula = newFormula.

For example, if the original formula is =A1, then I would like the new formula to be =IF(A1=0,"",A1). Using the Immediate window I confirmed that newFormula contains the correct string...the problem is adding that string to the cell.


Thanks!



Sub zero2blank()
'modify formulas for selected cells

Dim cel As Range
Dim selectedRange As Range
Set selectedRange = Application.Selection


For Each cel In selectedRange.Cells
exstFormula = cel.Formula
exstFormulaNoEqual = Replace(exstFormula, "=", "")

newFormula = Replace("=IF(yyy=0,"""",=yyy)", "yyy", exstFormulaNoEqual)

cel.Formula = newFormula

Next cel
End Sub

[Kenneth Hobs]

Remove the "=" from "=yyy".
If cel.HasFormula Then _
cel.Formula = Replace("=IF(yyy=0,"""",yyy)", "yyy", _
Replace(cel.Formula, "=", ""))

[desertsp]

Thank you!