Convert #DIV/0!

[mickeystanford_alumni]

Hi again everyone,
Is there any way to convert all #DIV/0! into blank cells?
I have a big file and every sheet has some #DIV/0!.
I don't want to go one by one putting the =IFERROR(,0) then:

Is there any way to get rid of them? Also it would work if in all #DIV/0! I insert the =iferror formula, however, in an automated way? Guess that's not possible right?

Txs for any ideas.

 

[Joe4]

The best thing to do would be to address/update all division formulas so this won't happen.
This happens when you try to divide by 0.
So, if you have a formula like this:

=A1/B1

if you rewrite it like this, it won't return that #DIV/0 error:

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

Note that if you are trying to use this value in further calculations, and provide a blank is problematic because it won't allow you to do mathematical functions on it, you may need to return a 0 instead of a blank like this:

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

 

[RoryA]

You could do it with code. What do you want as the last argument of the IFERROR?

 

[mickeystanford_alumni]

You could do it with code. What do you want as the last argument of the IFERROR?

for the IFERROR I would just need =IFERROR(formula already in place which is giving me the DIV error), 0)

 

[Joe4]

for the IFERROR I would just need =IFERROR(formula already in place which is giving me the DIV error), 0)

Just note that the IFERROR function handles ALL errors, which may or may not be a good thing (depending on your need).
For example, if you accidentally typed in a text value, and tried to divide that by a number, you will get a #VALUE error.
The IFERROR function will hide that error too. So you wouldn't be alerted of that typo error, as the IFERROR function will hide it.

 

[mickeystanford_alumni]

Just note that the IFERROR function handles ALL errors, which may or may not be a good thing (depending on your need).
For example, if you accidentally typed in a text value, and tried to divide that by a number, you will get a #VALUE error.
The IFERROR function will hide that error too. So you wouldn't be alerted of that typo error, as the IFERROR function will hide it.

that's okay as I don't have any other errors and I will not have. Its just the annoying thing to see the DIV errors in the same cells...

 

[RoryA]

This will add an IFERROR handler to all formula error cells. It could be amended to only handle specific errors if you prefer:

Sub FixFormulaErrors()
   Application.ScreenUpdating = False
   Dim ws As Worksheet
   For Each ws In ActiveWorkbook.Worksheets
      Dim errorCells As Range
      On Error Resume Next
      Set errorCells = Nothing
      Set errorCells = ws.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
      On Error GoTo 0
      If Not errorCells Is Nothing Then
         Dim cell As Range
         For Each cell In errorCells.Cells
            cell.Formula = "=IFERROR(" & Mid$(cell.Formula, 2) & ",0)"
         Next cell
      End If
   Next ws
   Application.ScreenUpdating = True
End Sub

 

[mickeystanford_alumni]

Awesome, txs so much