vba 如何检查多单元格范围是否包含任何错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16828458/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I check if a Multi-Cell Range contains any errors?
提问by mchangun
How can I use VBA to check if a Range in Excel contains any #N/A or other types of errors? I know how to do it for a single cell:
如何使用 VBA 检查 Excel 中的范围是否包含任何 #N/A 或其他类型的错误？我知道如何为单个单元格执行此操作：
IsError(Sheets("Main").Range("B1").value
IsError(Sheets("Main").Range("B1").value
but doing the same for a multi-cell range:
但对多单元格范围做同样的事情：
IsError(Sheets("Main").Range("A12:N32").value)
IsError(Sheets("Main").Range("A12:N32").value)
does not pick up an error. If I use the IsError function as a formula in one of the cells of the sheet, it works and picks up the error:
不会发现错误。如果我在工作表的一个单元格中使用 IsError 函数作为公式，它会起作用并发现错误：
=ISERROR(A12:N32)
=ISERROR(A12:N32)
Thanks in advanced.
提前致谢。
回答by
Please open a new workbookand try the code below.
This will write 3 values to column A, then iterate through the column and show a msgbox if an error occurs. Try it!
请打开一个新的工作簿并尝试下面的代码。
这将向 A 列写入 3 个值，然后遍历该列并在发生错误时显示一个 msgbox。尝试一下！
Option Explicit
Sub Throw_Errors()
Dim ws As Worksheet, rng As Range, i&: Set ws = Sheets(1)
ws.Range("A2") = "=100/0"
ws.Range("A3") = "=A2/0"
ws.Range("A4") = "=100/10"
For i = 2 To ws.Range("A" & Rows.Count).End(xlUp).Row
Set rng = ws.Range("A" & i)
If IsError(rng) Then MsgBox "error in row" & rng.Row
Set rng = Nothing
Next i
End Sub
Also check out this answerif you want to learn how to check for different types of errors!
如果您想学习如何检查不同类型的错误，请查看此答案！
