Friday 28 March 2008

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.

1 comment:

Alex said...

There is fine tool-Excel troubleshooting. This tool is made for troubleshooting Microsoft Excel, when Excel file lost all information,recover your information, that was damaged as a result of power failure, virus attack, hacker hostile actions or hardware failure, it will help you to recover all data just in three clicks of your mouse, even without your keyboard!