Multiple Criteria Filter VB

[Arjun.Singh]

Hello,

I have a sheet with data arranged in 5 column: State, Date, Quarter, Year and Funds.

The columns quarter and year are derived from using date.

I need a userform to gather the filters (user inputs of state & year) and the filter the data. What I intend to do is to write code to provide sum of funds for a given state & year (or a quarter if required) and save the results in a new file or print.

Ex.

State Date Year Quarter Funds
TX 11-11-2009 2009 Q3 10000$
TX 07-04-2008 2008 Q4 10000$
PA 26-02-2010 2010 3000$

What I do is filter on state (i.e. Texas,) then year, and if required, quarter to derive the rows meeting the criteria.

This is the code only for filtering:

If DTPicker1.Value = Year(Date) And ComboBox2.Value = "" Then

Worksheets("Review").Range("A1").AutoFilter Field:=1, Criteria1:=ComboBox1.Value 'State Funds All
MsgBox ("First IF")
lastrow = Range("A" & Rows.Count).End(xlUp).Row

Else

    If DTPicker1.Year <> Year(Date) And ComboBox2.Value = 0 Then
    
    Worksheets("Review").Range("A1").AutoFilter Field:=1, Criteria1:=ComboBox1.Value 'State
    Worksheets("Review").Range("A1").AutoFilter Field:=3, Criteria1:=Year(DTPicker1.Value) 'Till this year
    MsgBox ("Second IF")
    
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    
    Else
    
      If DTPicker1.Year <> "" And ComboBox2.Value <> 0 Then
      
      Worksheets("Review").Range("A1").AutoFilter Field:=1, Criteria1:=ComboBox1.Value 'State
      'MsgBox ("State")
      Worksheets("Review").Range("A1").AutoFilter Field:=3, Criteria1:=Year(DTPicker1.Value) 'Year
      Worksheets("Review").Range("A1").AutoFilter Field:=4, Criteria1:=ComboBox2.Value 'Quarter
      MsgBox ("Third IF")
      
      lastrow = Range("A" & Rows.Count).End(xlUp).Row

       ' If lastrow < 2 Then
       ' MsgBox ("No such data exists, please select different quarter")
       ' With ActiveSheet
        '     If .AutoFilterMode Then
         '   .ShowAllData
         '   End If
        'End With
        'End If
        
      End If
    End If
End If

End Sub

Many thanks for any help

 

[Arjun.Singh]

Help please, cant get this to work.