Excel find rows with #na [duplicate]

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
– BruceWayne, Commented Feb 23, 2017 at 15:29

Mihai Ovidiu Drăgoi · Accepted Answer · 2017-02-23 15:26:33Z

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, 2017 at 15:26

Mihai Ovidiu Drăgoi

1,3171 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Selrac Over a year ago

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?

Mihai Ovidiu Drăgoi Over a year ago

If I understand correctly, you just need to exit one of the FOR loops. use EXIT FOR. Further details here: stackoverflow.com/questions/9414969/excel-vba-exit-for-loop

Selrac Over a year ago

Perfect, thanks Mihai

Mihai Ovidiu Drăgoi Over a year ago

Glad it helped. Have a good day!

Collectives™ on Stack Overflow

Excel find rows with #na [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related