#N/A if statement

jaypatel

Active Member
Joined
Nov 25, 2002
Messages
385
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Advanced Filter with

=ISNA(C3)

as computed criterion.

Start Advanced Filter from within the sheet Unidentified.
 

north19701

Active Member
Joined
Jun 17, 2003
Messages
491
Code:
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

Active Member
Joined
Nov 25, 2002
Messages
385

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
jaypatel said:
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

Active Member
Joined
Nov 25, 2002
Messages
385

ADVERTISEMENT

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

Jay
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Jay:

Try ...
Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top