Excel - Clean Up the Div/0 Errors in a Worksheet

We are setting up some custom quoting setups in Excel.

One formula that we have is: D20/E20.

If nothing is in the respective cells we get an error: #DIV/0!

Excel does not like to divide by zero.

To correct the error, we need to use a conditional formula on top of the above. We use the following Macro to change all of the affected cells:

Dim rng As Range, cell As Range, fmla As String
Set rng = Cells.SpecialCells(xlCellTypeFormulas, 16)
For Each cell In rng
fmla = Right(cell.Formula, Len(cell.Formula) - 1)
cell.Formula = "=if(iserror(" & fmla & "), """"," & fmla & ")"
Next

The procedure is as follows:

1. Click on the Office Jewel and then the Excel Options button
2. Find and check "Show Developers tab in the Ribbon"
3. OK
4. Click the Developer tab
5. Click Macros
6. Type a name: CleanUpDiv0 [Enter]
7. Click Edit
8. Paste the above Macro in between the Sub and End Sub lines.
9. Close MS Visual Basic (should save automatically)
10. Click the Macros button
11. Make sure the CleanUpDiv0 is selected and click Run

The Div/0! errors will now be gone!

A screenshot of a properly formatted macro in MS Visual Basic:

Excel Clean Up Macro

This tip comes to us courtesy of Escalus near the bottom of the page on the MrExcel.com site: #Div/0.

One can also bring about the same results in Excel 2003 via the Tools menu.

Philip Elder
MPECS Inc.
Microsoft Small Business Specialists

*All Mac on SBS posts are posted on our in-house iMac via the Safari Web browser.