Meeting And/Or Criteria with an If Statement

Sun, 03 Aug 2008 18:22:02 Z

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.

Meeting And/Or Criteria with an If Statement

Mon, 04 Aug 2008 02:52:33 Z

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

Meeting And/Or Criteria with an If Statement

Wed, 06 Aug 2008 21:22:31 Z

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

Meeting And/Or Criteria with an If Statement

Thu, 07 Aug 2008 01:50:06 Z

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 mnw0307@gmail.com