CODEWITHSUNDEEP
excelvba
user3619789
user3619789

Reputation: 95

How to apply IFERROR to all cells in Excel

I have many cells that have #DIV/0! so I need to put the IFERROR function. Is there a way to apply this formula to all cells instead of putting the formula manually in every cell?

I tried this VBA code but I am looking for something more simple.

Sub WrapIfError()
Dim rng As Range
Dim cell As Range
Dim x As String

  If Selection.Cells.Count = 1 Then
    Set rng = Selection
    If Not rng.HasFormula Then GoTo NoFormulas
  Else
      On Error GoTo NoFormulas
        Set rng = Selection.SpecialCells(xlCellTypeFormulas)
      On Error GoTo 0
  End If

  For Each cell In rng.Cells
    x = cell.Formula
    cell = "=IFERROR(" & Right(x, Len(x) - 1) & "," & Chr(34) & Chr(34) & ")"
  Next cell

Exit Sub

'Error Handler
NoFormulas:
  MsgBox "There were no formulas found in your selection!"

End Sub

Can anyone help me?

Upvotes: 1

Views: 10683

Answers (1)

user4039065
user4039065

Reputation:

Perhaps one of these versions will be easier to teach.

Sub apply_Error_Control()
    Dim cel As Range

    For Each cel In Selection
        If cel.HasFormula Then
            'option 1
            cel.Formula = Replace(cel.Formula, "=", "=IFERROR(", 1, 1) & ", """")"
            'option 2
            'cel.Formula = "=IFERROR(" & Mid(cel.Formula, 2) & ", """")"
        End If
    Next cel

End Sub

I've supplied two ways to apply the IFERROR function as a 'wapper' for error control. To use the second option, comment the first and uncomment the second.

Select one or more cells and then run the macro; typically though Alt+F8 then Run from the worksheet.

Upvotes: 4

Related Questions