Find all "#DIV" formula's and repair

[VegasNath]

Is there any way to find all formula's (in a ws / wb) that create a #DIV error and repair them, by wrapping the formula in an 'IF(ISERROR...', for example?

[HansV]

Will this be used in Excel 2007 or later, or (also) in Excel 2003 or earlier?

[VegasNath]

I'm working 2003 at the moment, but will need to upgrade to 2007 soon.

[HansV]

The following will work with standard and array formulas, but it will bomb if there are protected sheets (it could be expanded to take those into account).

Code: Select all

Sub RepairDiv0()
  Dim wsh As Worksheet
  Dim rng As Range
  Dim strFormula As String
  ' Loop through worksheets
  For Each wsh In Worksheets
    ' Loop through cells with errors
    For Each rng In wsh.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
      ' Extra check is necessary for multi-cell array formulas
      If IsError(rng) Then
        ' Check for #DIV/0 error
        If rng = CVErr(2007) Then
          ' Formula without =
          strFormula = Mid(rng.Formula, 2)
          ' New formula
          strFormula = "=IF(ISERROR(" & strFormula & "),""""," & strFormula & ")"
          ' Check for array formula
          If rng.HasArray Then
            ' Apply new formula to all cells that have this array formula
            rng.CurrentArray.FormulaArray = strFormula
          Else
            ' Apply new formula
            rng.Formula = strFormula
          End If
        End If
      End If
    Next rng
  Next wsh
End Sub

[VegasNath]

Hans, this is great, Thanks.

If I wanted to use against just the active sheet, I could just remove the 'For Each wsh In Worksheets' / 'Next wsh', correct?

What about if I wanted to use against a selected range of cells only?

[HansV]

If you want it for the active sheet, you should indeed remove the For Each wsh In Worksheets and Next wsh lines, and add

Set wsh = ActiveSheet

at the beginning, in place of the For Each wsh line.

If you want to apply it to the current selection, remove the For Each wsh In Worksheets and Next wsh lines too, and change

For Each rng In wsh.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)

to

For Each rng In Selection.SpecialCells(xlCellTypeFormulas, xlErrors)

[VegasNath]

This is awesome, you have just saved me half a day!

I don't suppose it can be adjusted to repair #N/A & #VALUE aswell?

[HansV]

You asked specifically for #DIV/0, so that's what you got. It's actually easier to "repair" all errors. Simply delete the lines

' Check for #DIV/0 error
If rng = CVErr(2007) Then

and the corresponding

End If

[VegasNath]

Many Thanks Hans, this is a very useful macro!!

[VegasNath]

Hans,
I had manipulated your above macro to work with sheets / ranges earlier successfully. Now I would like to run it against all sheets in workbook. If i run the above macro (as posted) without any changes, I get RTE 1004 on line 'For Each rng In wsh.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)', no cells were found. Any idea why?

[HansV]

Excel throws an error if you refer to wsh.Cells.SpecialCells(xlCellTypeFormulas, xlErrors) if there are no cells with errors. The following version handles this:

Code: Select all

Sub RepairErrors()
  Dim wsh As Worksheet
  Dim rng As Range
  Dim rngErr As Range
  Dim strFormula As String
  ' Loop through worksheets
  For Each wsh In Worksheets
    On Error GoTo NextSheet
    Set rngErr = wsh.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    On Error GoTo 0
    ' Loop through cells with errors
    For Each rng In rngErr
      ' Extra check is necessary for multi-cell array formulas
      If IsError(rng) Then
        ' Formula without =
        strFormula = Mid(rng.Formula, 2)
        ' New formula
        strFormula = "=IF(ISERROR(" & strFormula & "),""""," & strFormula & ")"
        ' Check for array formula
        If rng.HasArray Then
          ' Apply new formula to all cells that have this array formula
          rng.CurrentArray.FormulaArray = strFormula
        Else
          ' Apply new formula
          rng.Formula = strFormula
        End If
      End If
    Next rng
NextSheet:
  Next wsh
End Sub

[VegasNath]

Still getting the same problem. When I step through, this is what I get:

Code: Select all

  For Each wsh In Worksheets
    On Error GoTo NextSheet
    Set rngErr = wsh.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
...jumps to
  Next wsh
...jumps back to
    On Error GoTo NextSheet
    Set rngErr = wsh.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
...RTE 1004, No cells were found

[HansV]

Yikes! Excel's way of handling this is abominable. Try this version:

Code: Select all

Sub RepairErrors()
  Dim wsh As Worksheet
  Dim rng As Range
  Dim strFormula As String
  On Error Resume Next
  ' Loop through worksheets
  For Each wsh In Worksheets
    ' Loop through cells with errors
    For Each rng In wsh.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
      ' Extra check is necessary for multi-cell array formulas
      If IsError(rng) Then
        ' Formula without =
        strFormula = Mid(rng.Formula, 2)
        ' New formula
        strFormula = "=IF(ISERROR(" & strFormula & "),""""," & strFormula & ")"
        ' Check for array formula
        If rng.HasArray Then
          ' Apply new formula to all cells that have this array formula
          rng.CurrentArray.FormulaArray = strFormula
        Else
          ' Apply new formula
          rng.Formula = strFormula
        End If
      End If
    Next rng
  Next wsh
End Sub

[VegasNath]

Thankyou Hans, it works brilliantly!