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?Answer1:
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 <a href="https://support.office.com/en-us/article/IFERROR-function-F59BACDC-78BD-4924-91DF-A869D0B08CD5" rel="nofollow">IFERROR function</a> as a <em>'wapper'</em> 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 <kbd>Alt</kbd>+<kbd>F8</kbd> then <kbd>Run</kbd> from the worksheet.