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 StringThe procedure is as follows:
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 & ")"
- Click on the Office Jewel and then the Excel Options button
- Find and check "Show Developers tab in the Ribbon"
- Click the Developer tab
- Click Macros
- Type a name: CleanUpDiv0 [Enter]
- Click Edit
- Paste the above Macro in between the Sub and End Sub lines.
- Close MS Visual Basic (should save automatically)
- Click the Macros button
- Make sure the CleanUpDiv0 is selected and click Run
A screenshot of a properly formatted macro in MS Visual Basic:
This tip comes to us courtesy of Escalus near the bottom of the page on the MrExcel.com site: #Div/0.
Excel Clean Up Macro
One can also bring about the same results in Excel 2003 via the Tools menu.
Microsoft Small Business Specialists
*All Mac on SBS posts are posted on our in-house iMac via the Safari Web browser.