search for data

[waffaa_qm]

hi please look to attached file

[StephenR]

Try this:

Sub x()

Dim c As Range
Dim l As Long
Dim str As String

str = "N/A"

With Sheets(1).Columns(2)
    Set c = .Cells(1, 1)
    For l = 1 To WorksheetFunction.CountIf(.Cells, str)
        Set c = .Find(what:=str, after:=c)
        Union(c, c.Offset(0, -1)).Copy Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Next l
End With

End Sub

[StephenR]

Or using AutoFilter:

Sub x()

Dim rng As Range

With Sheet1.Range("A1").CurrentRegion
    .AutoFilter field:=2, Criteria1:="n/a"
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
End With
Sheet1.AutoFilterMode = False

End Sub

[waffaa_qm]

thank you very much for all my freind who reply me
but please can you explain the code because I`m not professional in excel I`m just simple user
thanks

[StephenR]

Do you mean explain how to use the code?

If so, in your workbook, Press Alt & F11 to open the VB editor, Insert > Module and then paste the code into the large white space on the right hand side. I suggest change the name in the top line (currently x) to something meaningful. Then from the worksheet, Tools > Macros > Macros, select the macro and press Run.

BigBas has provided a formula solution.

[BigBas]

Waffa:

Although this can be done, there is not a simple formula to do it. That said, I am attaching a sample workbook that does what I believe you want. Sheet1 has entry information. Sheet 2 has the names that are N/A.

Check the file and let me know if you need any additional assistance.