如何将IFERROR应用于Excel中的所有单元格 [英] How to apply IFERROR to all cells in Excel

问题描述

我有很多单元格的#DIV/0！所以我需要把IFERROR函数.有没有办法将此公式应用于所有单元格，而不是手动将公式放在每个单元格中?

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?

我尝试了此VBA代码，但我正在寻找更简单的方法.

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

有人可以帮助我吗?

推荐答案

也许其中一个版本更容易教授.

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.

选择一个或多个单元格，然后运行宏；通常是通过 Alt + F8 ，然后是工作表中的 Run .

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

这篇关于如何将IFERROR应用于Excel中的所有单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！