Excel VBA find #N/A

Question

I have a worksheet with a formula that returns =NA() under certain conditions. Using VBA, I'd like to find #N/A, but I haven't been able to tweak this answer.

lastrow = .Cells.Find(What:="*", _
                  After:=.Range("A1"), _
                  Lookat:=xlPart, _
                  LookIn:=xlFormulas, _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious, _
                  MatchCase:=False).Row

I've tried What:=CVErr(xlErrNA), What:=xlErrNA and also What:="#N/A" to no avail.

As an added difficulty, I have to take Dutch into account, so What:="#N/A" probably wouldn't even work in Dutch.

Note. I'm asking this question out of curiosity as I haven't found a method online. At this moment, I'm calculating which cells contain =NA()

I would think that using some sort of `Range.SpecialCells()` call will do the trick for you, but it's not coming to me off the top of my head. Maybe take a look down that path. — FreeMan, Apr 06 '15 at 14:55

score 4 · Accepted Answer · answered Apr 06 '15 at 14:09

4

You're looking in the cell formulas. Try looking in the cell values, then I can get this to work using the string "#N/A" for the What argument:

lastrow = .Cells.Find(What:="#N/A", _
            After:=.Range("A1"), _
            Lookat:=xlPart, _
            LookIn:=xlValues, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False).Row

answered Apr 06 '15 at 14:09

David Zemens

53,033
11
81
130

This seems to work. I'll probably have to get the correct Dutch text though. – GUI Junkie Apr 06 '15 at 19:34
@GUIJunkie if you need it to work for both languages Dutch and English, that would also be possible with some tweaking. Let me know if you have any trouble with it :) – David Zemens Apr 06 '15 at 20:05

score 1 · Answer 2 · answered Apr 06 '15 at 16:09

1

Please try the below code , You should use the loop to read the error.

Sub Test()

If Range("A2").Text = "#N/A" Then
    MsgBox "hi"
End If

End Sub

answered Apr 06 '15 at 16:09

Swapnil Wankhede

29
1
4

Thanks a lot. I lost 1 hour, then I got it. – Abu Nayeem May 02 '16 at 17:03

score 1 · Answer 3 · answered Apr 06 '15 at 16:20

Hi I have another solution, You have paste these formulas values into another columns as text and

then use the replace code,

Please try the below code.

Sub Tst()

    Columns("C:C").Select
    Selection.Copy
    Range("D1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub

The column C Contains formulas values (Error) .

score 0 · Answer 4 · answered Apr 06 '15 at 11:26

0

The problem here is that you're looking for "#N/A" as a value of the cell and it's not the value it's a error indicator, so if you're trying to find a cell wich gives you a error you have to use something like this:

If WorksheetFunction.IfError("Put here a loop to read the cells;"Error")="Error" then

"Write what you desire for cells with error"

end if

answered Apr 06 '15 at 11:26

Ygor Yansz

176
1
4
12

Looping through ranges is slow which is why I'd like to use `find` instead. – GUI Junkie Apr 06 '15 at 11:29

Excel VBA find #N/A

4 Answers4