Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Worthington, Ohio, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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
    <pre>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</pre>


  3. #3
    New Lounger
    Join Date
    Dec 2002
    Location
    Dumfries, Virginia, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  4. #4
    Star Lounger
    Join Date
    Jun 2002
    Location
    Worthington, Ohio, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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)

  6. #6
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Eliminating #DIV/0 error from entire sheet (Ex

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

    <pre>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(xlCell TypeFormulas, 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
    </pre>


    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!

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Eliminating #DIV/0 error from entire sheet (Ex

    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

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Eliminating #DIV/0 error from entire sheet (Ex

    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 ",,".

  10. #10
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    "...then go to the Cell errors as: ..." - Can't find this on my Excel 2000 - or is it an XP thing? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Tony
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  12. #12
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    __________________________________________________ ___________________________
    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
    __________________________________________________ ___________________________


    <img src=/S/note.gif border=0 alt=note width=20 height=20> YES, This option this available only in XP, but does work at the time of printing, not for view.

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Eliminating #DIV/0 error from entire sheet (Ex

    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:

    <pre>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
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •