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 Intege
Dim iRow1 As Intege
Dim iRow2 As Intege
Dim ws1 As Workshee
Dim ws2 As Workshee
Dim erow As Intege

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

'copy from sheet1 to sheet
Do Until ws1.Cells(iRow1, 28) = "END
If ws1.Cells(iRow1, 28) = "Closed" Or "Cancelled" The
For iCt = 1 To 2
ws2.Cells(iRow2, iCt) = ws1.Cells(iRow1, iCt
Next iC
iRow2 = iRow2 +
End I
iRow1 = iRow1 +
Loo

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

End Su

Thanks
Jay Baxte

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

You're using the "Or" operator improperly. You need to
include the expression both times:

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

-Brad
-----Original 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

.

Thank you Chip

It worked like a charm

Jay Baxter

Thanks Brad

I got it working now. I'm not too familiar with VB syntax.. But alas all works for now hehe

Jay