#N/A if statement

[jaypatel]

Hi,

I have some #N/A's in my spreadsheet (specifically in column C)....... what would be the code if #N/A exists in any row, then cut and paste to sheet called "Unidentified".....

so if c3 = #N/A then cut row 3 to sheet called "unidentified".

Cheers

Jay

 

[Aladin Akyurek]

Advanced Filter with

=ISNA(C3)

as computed criterion.

Start Advanced Filter from within the sheet Unidentified.

 

[jaypatel]

say if you wanted to do this automatically via a macro please.

Jay

 

[north19701]

Sub Macro1()
Range([c1], [c65536].End(xlUp)).AutoFilter Field:=1, Criteria1:="#N/A"
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
    Destination:=Sheets("unidentified").Range("a1")
End Sub

 

[jaypatel]

sorry to be a pain.......but could the macro not involve the advanced autofilter, as it still stays in the main spreadsheet, and i just need to cut and paste all the #N/A's in the "unidentified" spreadsheet.

Cheers

Jay

 

[Aladin Akyurek]

sorry to be a pain.......but could the macro not involve the advanced autofilter, as it still stays in the main spreadsheet, and i just need to cut and paste all the #N/A's in the "unidentified" spreadsheet.

Cheers

Jay

Why don't you record a macro while applying Advanced Filter wih computed criterion?

 

[jaypatel]

but i dont need the advanced filter.... I require the cut and paste method please.

Jay

 

[Yogi Anand]

Hi Jay:

Try ...

Sub yJayPatel()
' 02/08/2004 by Yogi Anand
'******************************************************************
'Assuming your Source data is in Sheet1 and starts with cell A1
'   and You want to write the filtered rows with #N/A in column C
'   to sheet name "unidentified" starting with cell A1
'******************************************************************
    Sheets("Sheet1").Range("A1").AutoFilter Field:=3, Criteria1:="#N/A"
        Range([a65536].End(xlUp), [a65536].End(xlUp).End(xlUp)(2, 1)).EntireRow.Select
        Selection.SpecialCells(xlCellTypeVisible).Copy
    Sheets("unidentified").Select
        Range("A1").Select
        ActiveSheet.Paste
    Sheets("Sheet1").Select
        Application.CutCopyMode = False
        Selection.EntireRow.Delete
    ActiveSheet.ShowAllData
End Sub

Does it help?

 

[jaypatel]

perfect.

Cheers mate

Jay