type mismatch in this code

[Guest]

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

 

[Chip Pearson]

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


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.

 

[Brad]

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.

 

[Guest]

Thank you Chip

It worked like a charm

Jay Baxter

 

[Guest]

Thanks Brad

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

Jay