Meeting And/Or Criteria with an If Statement

Question

Hello,

 

I have a spreadsheet in Excel 2003 with two columns of data.  Column A is labeled "Work" and Column B is labeled "Type of Work".  The macro that I have activates cell A2 and implements a Do While Loop to scroll through each row on the spreadsheet that contains data.  Inside this Do While Loop, the macro is looking for rows that meet certain criteria in columns A and B.  Column A must equal the variable work, which is defined by the user using an input box and coumn B must either contain the text "PM" or "MP".  I am trying to implement an If Then statement that looks for rows that contain the variable work in column A and either "PM" or "MP" in column B, but I don't know how to specify the or just for column B.  How would I go about doing this?

 

Thanks.   

Answer 1

Using AdvanedFilter is recommended.

 

Code Snippet

Sub myAdFilter()
Dim srSht As Worksheet
Dim dtSht As Worksheet
    Set srSht = Sheets("Sheet1")
    Set dtSht = Sheets("Sheet2")
    dtSht.Cells.ClearContents
    With srSht
        .Columns("A:B").AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=.Range("H1:I3"), CopyToRange:=dtSht.Range("A1"), _
            Unique:=False
    End With
    dtSht.Activate
End Sub

 

here is the example.

http://www.box.net/shared/uqtw80w8wc

 

Answer 2

I want the macro to copy the entire row containing the cells that meet this critera.  Can this still be done with AdvancedFilter?

Answer 3

Yes, it do be done with AdvancedFilter.

check out the code below...

 

Code Snippet

Sub myAdFilter()
Dim srSht As Worksheet
Dim dtSht As Worksheet
    Set srSht = Sheets("Sheet1")
    Set dtSht = Sheets("Sheet2")
    dtSht.Cells.ClearContents
    'This example is assuming that
    '1. the data table's lefttop cell is located at A1cell
    '2. the criteria range is located at H1:I3
    With srSht
        .Range("a1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=.Range("H1:I3"), CopyToRange:=dtSht.Range("A1"), _
            Unique:=False
    End With
    dtSht.Activate
End Sub

 

 

and example is here

http://www.box.net/shared/com7vfvw4c

 

any problems, please email an example to  [e-mail]