Jay,

Change

If ws1.Cells(iRow1, 28) = "Closed" Or "Cancelled" Then

to

If ws1.Cells(iRow1, 28) = "Closed" Or .Cells(iRow1, 28)
="Cancelled" Then



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Jay Baxter" wrote in
message
...
I am scanning a column in sheet1 and looking for "Closed" or
"Cancelled"
If i find closed or cancelled, the whole row that it finds it
on gets moved to sheet2.
After that... Any row that contained "Closed" or "cancelled"
gets deleted from sheet1.

When i run the macro I get "Type Mismatch 13" on "If
ws1.Cells(iRow1, 28) = "Closed" Or "Cancelled" Then". The same
error would occur in "If ws1.Cells(iCt, 28) = "Closed" Or
"Cancelled" Then ws1.Rows(iCt).Delete"

If i remove the ---Or "Cancelled" in both places, the function
works and searches for closed only. But i need it to look for
cancelled items too. Any help would be greatly appreciated.


This is the code i'm using:

Sub ClosedRoutine()

Dim iCt As Integer
Dim iRow1 As Integer
Dim iRow2 As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim erow As Integer

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
iRow1 = 6
erow = 5
While ws2.Cells(erow, 28) < "": erow = erow + 1: Wend
iRow2 = erow

'copy from sheet1 to sheet2
Do Until ws1.Cells(iRow1, 28) = "END"
If ws1.Cells(iRow1, 28) = "Closed" Or "Cancelled" Then
For iCt = 1 To 29
ws2.Cells(iRow2, iCt) = ws1.Cells(iRow1, iCt)
Next iCt
iRow2 = iRow2 + 1
End If
iRow1 = iRow1 + 1
Loop

'delete from sheet1
For iCt = iRow1 To 2 Step -1
If ws1.Cells(iCt, 28) = "Closed" Or "Cancelled" Then
ws1.Rows(iCt).Delete
Next iCt

End Sub


Thanks,
Jay Baxter