Excel find rows with #na

Question

I'm trying to find a way to find rows with #na so that I can copy the row and past it in another tab so that the error can be analysed.

So far I have the following, but I have some problems

Dim iCntr As Long
Dim lastColumn As Long
For iCntr = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
    For lastColumn = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column To 1 Step -1
        If Cells(iCntr, lastColumn).Text = "#N/A" Then
            MsgBox "row" & iCntr
        End If
    Next
Next

First of all, the code doesn't seem to find cells with #N/A errors. I'm not sure why.

Also, I have 60K rows and 50 columns and trying to run the code above is taking very long. I would like for the code jump the next line as soon as a #N/A is found as there is no need to check the rest of the columns

Any ideas on how to resolve the previous points?

At the moment I'm just showing a message box, but I the intention is to copy all the row once the #N/A is found and copy it to another page one row after the other.

If you want to select error cells, I believe you could just use `range("A:A").SpecialCells(xlCellTypeFormulas,xlErrors)` — BruceWayne, Feb 23 '17 at 15:29

score 3 · Accepted Answer · answered Feb 23 '17 at 15:26

3

You are trying to find the string "#N/A" - that will not work. Excel has the function IsNA(), and in VBA you can use Application.WorksheetFunction.IsNA(rngToCheck.Value)

Hope this helps.

answered Feb 23 '17 at 15:26

Mihai Ovidiu Drăgoi

1,307
1
10
16

Thanks Mihai, that helped (If Application.WorksheetFunction.IsNA(Cells(iCntr, lastColumn).Value) Then). Would you know how to make it skip to the next row and stop searching on the rest of the columns so that I can speed up the process? – Selrac Feb 23 '17 at 15:44
1

If I understand correctly, you just need to exit one of the FOR loops. use EXIT FOR. Further details here: http://stackoverflow.com/questions/9414969/excel-vba-exit-for-loop – Mihai Ovidiu Drăgoi Feb 23 '17 at 15:46
Perfect, thanks Mihai – Selrac Feb 23 '17 at 15:52
Glad it helped. Have a good day! – Mihai Ovidiu Drăgoi Feb 23 '17 at 15:56

Excel find rows with #na

1 Answers1