1723

How to apply IFERROR to all cells in Excel

Question:

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.

Recommend

  • Excel: list ranges targeted by INDIRECT formulas
  • VBA Nested If, Then, Else Loop That Copies Non-Matching Entries
  • Transpose a range row
  • How to save a text file (CSV) with UTF-8 without BOM encoding in VBA (Excel)?
  • VBA to convert texts to numbers except formula and non-numeric texts
  • working with Nested IF statement in excel
  • Check if one value in one column is in another column
  • Pivot table calculated Field from count of values
  • VBA vlookup with defined range and file from other workbook
  • How to get (-8)^0.333333 = -2 in MATLAB?
  • How to check a string does not start with a number in Batch?
  • How to calculate total Fridays between two dates in excel on weekly, biweekly and monthly basis?
  • Setting Unknown Array Boundaries and Loop
  • Finding number of samples in a .wav header
  • How to delete column from range if cell contains specific value in VBA/Excel
  • Watir::Exception::MissingWayOfFindingObjectException: invalid attribute: :css
  • What exactly is the height of modalPresentationStyle - FormSheet on iPad?
  • calculating number of bytes of each row in an image
  • Excel distinct count with conditions
  • How can I restyle a word when rendering a pdf with pdf.js?
  • Using Sax parsing to edit and write XML in VB6
  • Conversion from string “a” to type 'Boolean' is not valid
  • Excel's Macro-Recorder usage
  • Swift: Switch statement fallthrough behavior
  • Django: Count of Group Elements
  • Excel - Autoshape get it's name from cell (value)
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • vba code to select only visible cells in specific column except heading
  • Circular dependency while pushing http interceptor
  • Data Validation Drop Down Box Arrow Disappearing
  • Linker errors when using intrinsic function via function pointer
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • How to set the response of a form post action to a iframe source?
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Change div Background jquery
  • Qt: Run a script BEFORE make
  • apache spark aggregate function using min value
  • Sorting a 2D array using the second column C++
  • reshape alternating columns in less time and using less memory