如何将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屋!