desertsp
11-21-2016, 11:17 AM
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
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