vba Excel 宏：忽略错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22193118/
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
Excel macro: ignore error
提问by user2774993
I'm using vba code to vlookup values from an array. Some of the results are "#N/A" and there will be a green triangle on the left top of the cell. I want to remove the green triangle (ignore the error) automatically, but the "#N/A" result should be remained, just remove the green triangle.
我正在使用 vba 代码从数组中查找值。一些结果是“#N/A”，并且在单元格的左上角会有一个绿色三角形。我想自动删除绿色三角形（忽略错误），但应该保留“#N/A”结果，只需删除绿色三角形即可。
Anyone knows how to do this?
任何人都知道如何做到这一点？
Thanks in advance.
提前致谢。
回答by Profex
It seems as though you can only ignore one cell at a time or it will give you an Application-defined or object-defined error
好像一次只能忽略一个单元格，否则它会给你一个应用程序定义或对象定义的错误
So, you'll need something like this...
所以，你需要这样的东西......
Dim Cell As Range, Target As Range
Set Target = Range("C10:D17")
For Each Cell In Target
Cell.Errors(xlEvaluateToError).Ignore = True
Next
Also, see XlErrorChecks Enumeration (Excel)for a list of all the errors.
此外，请参阅XlErrorChecks Enumeration (Excel)以获取所有错误的列表。
回答by Tim
You need code that looks something like this:
您需要看起来像这样的代码：
Dim r as Range
...
r.Errors.Item(xlEvaluateToError).Ignore = True
This will clear the error triangle (xlEvaluateToError equals 1, which is why the previous person had Item(1) in their answer).
这将清除错误三角形（xlEvaluateToError 等于 1，这就是前一个人的答案中有 Item(1) 的原因）。
The other errors that can occur can be cleared in a similar way, by changing the number of the Item, e.g. I've used a block of code like this to put a value (that might look like a number, but I want to ensure is treated as text) into a cell:
可能发生的其他错误可以通过类似的方式清除，通过更改项目的编号，例如我使用了这样的代码块来放置一个值（它可能看起来像一个数字，但我想确保被视为文本）放入一个单元格中：
With Target_Sheet.Cells(RowNum, ColNum)
.NumberFormat = "@" ' Force the cell to be treated as text
.Value = whatever_value_im_trying_to_set
.Errors.Item(xlNumberAsText).Ignore = True
End With
See the Excel help for Errors.Item Property for the full list of error codes you can check or ignore.
有关您可以检查或忽略的错误代码的完整列表，请参阅 Errors.Item 属性的 Excel 帮助。
You can also query the value of .Errors.Item(xlNumberAsText).Value(or the other error codes) to see if the error is occurring, but you can't set .Value to clear the error.
您也可以查询.Errors.Item(xlNumberAsText).Value（或其他错误代码）的值以查看是否发生错误，但您不能设置 .Value 来清除错误。
回答by steveo40
If you want the green triangle to go away you need to set the Error.Ignore property to true. For example, if you have a formula such as "=1/0" in cell A1, then in VBA you could do something like this:
如果您希望绿色三角形消失，您需要将 Error.Ignore 属性设置为 true。例如，如果您在单元格 A1 中有一个诸如“=1/0”之类的公式，那么在 VBA 中您可以执行以下操作：
Dim r as Range
Set r = Range("A1")
r.Errors.Item(1).Ignore = True
And the green triangle will go away.
绿色三角形将消失。
回答by Alpha
You can use below formula:
您可以使用以下公式：
=IFERROR(VLOOKUP([Your Value], [Your Range], [Your Column], FALSE), NA())
=IFERROR(VLOOKUP([您的价值], [您的范围], [您的列], FALSE), NA())
