+ Reply to Thread
Results 1 to 2 of 2

Auto Filtering Macro that Filters for more than one item

Hybrid View

  1. #1
    Registered User
    Join Date
    11-06-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    4

    Auto Filtering Macro that Filters for more than one item

    I have a macro that auto sorts one worksheet and copies the filtered data to another.

    What I need is for when my macro asks for input on what department to filter I need it to look for the one they list plus another.

    Example: There is a department called QA and QC. When they type in QC I need the macro to filter for QC & QA. I need this to work both ways.

    Here is part of my code

    Formula: copy to clipboard
     
    org = InputBox(Prompt:="Enter Organization Here. (Ops, QA, QC, HR, etc.)", _
    Title:="ENTER ORGANIZATION", Default:="Enter Organization Here")
    If org = Cancel Then
    Exit Sub
    End If

    On Error GoTo Canceled
    tBeg = InputBox(Prompt:="Enter First Raised Date of Range.(YYYY, MM, DD)", _
    Title:="ENTER THE DATE", Default:="Year, Month, First day")
    On Error GoTo Canceled
    tEnd = InputBox(Prompt:="Enter End Raised Date Range. (YYYY, MM, DD)", _
    Title:="ENTER THE DATE", Default:="Year, Month, Last day")

    With Worksheets("xxxxx")
    .AutoFilterMode = False
    .Range("A5").AutoFilter Field:=5, Criteria1:="=" & org
    .Range("A5").AutoFilter Field:=2, Criteria1:=">=" & tBeg, _
    Operator:=xlAnd, Criteria2:="<=" & tEnd


    Thanks for the help

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Auto Filtering Macro that Filters for more than one item

    You'll need to test the entry given by the user for org:

    .Range("A5").AutoFilter Field:=5, Criteria1:="=" & org
    ....will need to change to:

        Select Case org
            Case "QC", "QA"
                .Range("A5").AutoFilter Field:=5, Criteria1:="=QC", Operator:=xlOr, Criteria2:="=QC"
            Case Else
                .Range("A5").AutoFilter Field:=5, Criteria1:="=" & org
        End Select
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1