Geek asks and answers

Check the value "# N / A" in vba in the range

I want to check value #N/Ain excel using 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

But when he went on MyTab(i, j).value <> CVErr(xlErrNA), I have a mistake 13(type error), and I did not find why.

Can anybody help me plz?

+5
source share
2 answers

First you need to check that the cell contains an error:

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

If you do not want to know the type of error (# N / A, # DIV / 0 !, etc.), you can also replace your test:

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

If you need to check the type of error, 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
+9
source

Another way to check for error

If CVErr(MyTab(i, j).Value) = CVErr(xlErrNA)
+4
source

More articles:


All Articles