Macro to convert formulas to "IF(ISERROR)

Posted by Dwight on November 07, 2001 6:19 AM

Looking for a macro to quickly zap the unsightly "#/DIV/0!" message. Tried to record a macro which will:
1) Go to the formula for the active cell
2) Go to the beginning of the formula and delete the leading "=" ("home, delete")
3) Copy the rest of the formula to the clipboard ("shift/end, copy")
4) Return to the beginning of the formula ("home")and enter: "=IF(ISERROR("
5) Paste in the original formula, enter: "),""(
6) Again paste in the original formula followed by two closing parentheses.
7) Entered the result and moved down one cell
8) Thus "=B7/G8" would be converetd to "=IF(ISERROR(B7/G8),"",(B7/G8))

Of course I failed miserably. Tried to record with relative reference button selected. resulting macro doesn't work:
ActiveCell.FormulaR1C1 = "=IF(ISERROR(R[-2]C/R[-1]C),"""",(R[-2]C/R[-1]C))"
ActiveCell.Offset(1, 0).Range("A1").Select

Can someone help?
Thanks in advance.
Dwight

Posted by Barrie Davidson on November 07, 2001 6:28 AM

Dwight, the following code will make the necessary changes to the cells you have selected prior to running the macro.

Sub PrettyUp()
' Written by Barrie Davidson
Dim FormulaChanged As String

For Each Cell In Selection
FormulaChanged = Mid(Cell.Formula, 2)
FormulaChanged = "=IF(ISERROR(" & FormulaChanged & "),""""," & FormulaChanged & _
")"
Cell.Formula = FormulaChanged
Next Cell
End Sub

Regards,
BarrieBarrie Davidson

Posted by Dwight on November 07, 2001 6:57 AM

Wonderful! Thanks, Barrie NT