检查“#N/A"vba 中的值进入一个范围 [英] check a "#N/A" value in vba into a range

问题描述

我想用 VBA 将 #N/A 值检查到 excel 中.所以经过一些研究，我做了这个代码:

I want to check a #N/A value into excel with VBA. So after some research, I made this code :

Set MyTab = Range(name)
If (Not IsEmpty(MyTab.value)) And (MyTab(i, j).value <> CVErr(xlErrNA)) Then
   Call BuildRequest(False, id, MyTab, i, j)
End If

但是当它通过 MyTab(i, j).value <>CVErr(xlErrNA) 我有一个错误 13(type error) 并且我没有找到原因.

But when it passed on MyTab(i, j).value <> CVErr(xlErrNA) I have an error 13(type error) and I don't find why.

有人可以帮我吗?

推荐答案

您首先需要检查单元格是否包含错误:

You first need to check that the cell does contain an error:

If IsError(MyTab(i, j).Value) Then
    If MyTab(i, j).Value <> CVErr(xlErrNA) Then

除非您确实想知道错误类型(#N/A、#DIV/0！等)，否则您最好将测试替换为:

Unless you do want to know the type of error (#N/A, #DIV/0!, etc) , you might as well replace your test with:

If (Not IsEmpty(MyTab.value)) And (Not IsError(MyTab(i, j).value)) Then

如果需要检查错误类型，可以这样写:

If you need to check the error type, you can write:

Dim shouldBuildRequest As Boolean

shouldBuildRequest = Not IsEmpty(MyTab.value)
If IsError(MyTab(i, j).Value) Then
    shouldBuildRequest = shouldBuildRequest AND (MyTab(i, j).Value <> CVErr(xlErrNA))
End If

If shouldBuildRequest Then
    Call BuildRequest(False, id, MyTab, i, j)
End If

这篇关于检查“#N/A"vba 中的值进入一个范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！