Eliminating #DIV/0 error from entire sheet (Excel 2000)

Topic

New Reply

I know that you can do this cell by cell. However, I have my entire workbook built and would like to replace the error with “0” throughout the entire sheet. Can I use a conditional format to find all of these and then replace with a “0”? My worksheets are entitled “Week 1” “Week 2”, etc etc.

Thanks!

Reply | Quote

Replies

Conditional formatting can be used to “hide the value” by coloring the text the same color as the background, but you can NOT change the VALUE from and error to “0”. BEWARE of this method if the background is colored. When printedon B/W printers the TEXT will be black so it will be printed, even if the color is eg yellow on screen.

Here is a macro which will replace the div/0 errors with the value zero.
IMPORTANT: You will LOSE the formula in the cell if this is run! and it will be 0 even if other values change.

You could modify the code to change the FORMULA if desired.

Steve

Option Explicit
Sub ReplaceDivByZero()
    Dim rng As Range
    Dim rCell As Range
    Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)

    For Each rCell In rng
        If rCell.Value = CVErr(xlErrDiv0) Then
            rCell.Value = 0
        End If
    Next
End Sub

Reply | Quote

I’m not exactly sure how your spreadsheet is set up, but you may be able to do a find/replace.

Reply | Quote

The find and replace does not work, unfortunately. It doesn’t recognize the error and looks only at an actual value in the cell. Unless I have to type in something other than “#DIV/0” in the “find” box.

Reply | Quote

I suspect that you have Formulas selected in the “Look in: “box in Find; change this to Values and it should work as you want.

Presumably the error is from dividing by 0 in a formula, say:

=A1/B1.

If so to avoid the problem in the future try this:

=IF(B1=0,0,A1/B1)

Reply | Quote

You may desire to replace the error with a blank. This is done through File, Page Setup, Sheet, then go to the Cell errors as: and click on the down button to select Blank. While this will replace the error with a blank the error is still there.

Hope this helps

Reply | Quote

“…then go to the Cell errors as: …” – Can’t find this on my Excel 2000 – or is it an XP thing?

Tony

Reply | Quote

I’m afraid this is new in Excel 2002 (XP). This new option only hides error values in a printout (and in print preview), though; they will still be displayed on screen. You can use conditional formatting to hide error values on screen (see the first reply in this thread), or use one of the macros posted in this thread.

Reply | Quote

_____________________________________________________________________________
You may desire to replace the error with a blank. This is done through File, Page Setup, Sheet, then go to the Cell errors as: and click on the down button to select Blank. While this will replace the error with a blank the error is still there
_____________________________________________________________________________

YES, This option this available only in XP, but does work at the time of printing, not for view.

Reply | Quote

If Steve doesn’t mind me piggybacking on his code, you could try the following:

Option Explicit
Sub ReplaceDivByZero()
    Dim rng As Range
    Dim sRCF As String
    Dim rCell As Range
    On Error Resume Next
    Set rng = ThisWorkbook.ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    For Each rCell In rng
        If rCell.Value = CVErr(xlErrDiv0) Then
            sRCF = Right(rCell.Formula, Len(rCell.Formula) - 1)
            rCell.Formula = "=IF(ISERROR(" & sRCF & "),,(" & sRCF & "))"
        End If
    Next
End Sub

I have not tested it with complex formulas, but it does seem to work OK with something simple like “=B1/A1” where A1 = 0…in other words, USE AT YOUR OWN RISK!

Reply | Quote

I don’t mind at all. I think your code is a great suggestion. It keeps the formula, yet gets rid of the error. I took the lazy approach to just answer the question.

Steve

Reply | Quote

Well, while that works in this particular instance, the best solution by far is to build the ifs into the original formula. One thing that hung me up was trying to put a ‘ “—“‘ in place of the “,,”. Works best for me to have “—” or some other text instead of “0” when catching DIV errors. Using the code, I kept getting an “Application or Object Defined Error” and finally gave up on figuring out what it was, I think it was purely syntax on my part on trying to put the “—” in place of the “,,”.

Reply | Quote

This solution will fix all of the cells that have an error when it is run. However, the problem can come back if future data entry puts a zero in a cell that is used as the denominator of a cell that did not originally have an error displayed.

A better solution might be to allow the user to select all of the cells that might have the problem and run this modification of your code:

Sub ReplaceDivByZero()
    Dim sRCF As String
    Dim rCell As Range
    On Error Resume Next
    For Each rCell In Selection
        sRCF = Right(rCell.Formula, Len(rCell.Formula) - 1)
        rCell.Formula = "=IF(ISERROR(" & sRCF & "),,(" & sRCF & "))"
    Next
End Sub

Reply | Quote