HELP - NEED MACRO TO ELIMINATE "#DIV/0!" VALUE IN CELLS

Posted by EDDIE G on October 09, 2001 10:00 AM

I NEED A MACRO THAT WILL SCAN A LARGE WORK SHEET AND CHANGE ALL THE #DIV/0! SYMBOLS WITHIN THE CELLS TO A VALUE OF 0 (ZERO).

Posted by Damon Ostrander on October 09, 2001 11:03 AM

Hi Eddie,

Here is a macro that will scan all the selected cells and replace the #DIV/0! results with 0. If you want to run it on the entire sheet, just select all the cells with a Ctrl-A. Also bear in mind that it must replace any formula in the problem cells, since it would be an incorrect result for the formula to yield anything other than #DIV/0!.

Sub DelDiv0()
' Removes the #DIV/0! values from all cells in the selected
' range and replaces them with a zero value
Dim Cel As Range
For Each Cel In Selection
If IsError(Cel.Value) Then
If Cel.Value = CVErr(xlErrDiv0) Then Cel.Value = 0
End If
Next Cel
End Sub

Happy computing.

Damon

Posted by g.k. atkins on October 09, 2001 11:09 AM

Instead of a macro, why not simply edit the formulas to include error checking?

For example: Take =a3/b3 and change it to
=if(iserr(a3/b3),0,a3/b3)

Then you never have to worry about seeing the error appear. Sure, it may take more work at first, but if the sheet is used in the future and data changes, you won't have to worry about running a macro or worry about whether it fails.