passing calendar date into a filter criteria

megatronixs · Jun 2nd 2016

Hi all,

I have a sheet with a report that I build using VBA to filter and get the value into the sheet. Normaly I use the filter in Excel 2010 with the criteria "xlFilterYesterday" but, some times I need to put a different date.
I added a calendar control in the main sheet where i have the buttons to start the macros in the workbook. I tried many things, but I just don't get it how to pass the date to a filter from the calendar control

How can I use Calendar1 to pass the date to Criteria1.

Code

Sub Cases_Received()
    Dim mySubtotal As Double
    Sheets("Raw_Data").Activate
    With Sheets("Raw_Data")
        .AutoFilterMode = False
    Rows("1:1").Select
    Selection.AutoFilter
    
   
    Worksheets("Raw_Data").Range("$A$1:$DB$56000").AutoFilter Field:=38, Criteria1:=xlFilterYesterday, Operator:=xlFilterDynamic
        
            End With
                mySubtotal = Application.WorksheetFunction.Subtotal(3, Worksheets("Raw_Data").Range("A2:A56000"))
        Worksheets("Snap_Shot").Range("B3").Value = mySubtotal
        
End Sub

Display More

Greetings.

**cytop** · Jun 2nd 2016

Re: passing calendar date into a filter criteria

I can't say anything about the calendar control, you never mentioned which one, the data it returns, or anything. But in general, VBA always seems to have issues with Dates in a filter. If you are trying to filter for 1 day then use 'Date Between' with the first date being 'Greater than or equal to', your actual date and the second being 'Less Than' the date +1.

The explanation sounds worse that what it is...

Code

Sub z()
   Dim Date1            As Date
   Dim Date2            As Date


   '// You want to filter for 3 days ago
   Date1 = Date - 3
   Date2 = Date - 2
 
   ActiveSheet.Range("$A$1:$G$15").AutoFilter Field:=4, Criteria1:=">=" & Format(Date1, "YYYY-MM-DD"), Operator:=xlAnd, Criteria2:="<" & Format(Date2, "YYYY-MM-DD")
   
End Sub

Display More

megatronixs · Jun 2nd 2016

Re: passing calendar date into a filter criteria

Hi Cytop,

Thanks.
I use the active x control calendar 11.0.
Maybe I can use it to add this to a cell in the sheet and then create a variable with the reference of the cell and use that for the filter.
So far I had no problems when using the date directly in the filter like this:

Code

Operator:=xlFilterValues, Criteria2:=Array(2, "12/31/2015")

, but I would like to be able to make a choice and not change all in the VBA editor if I wanted different data than yesterday.

Greetings.

**cytop** · Jun 2nd 2016

Re: passing calendar date into a filter criteria

The calendar presumably updates a cell, then use that cell as the filter criteria.

Attached is an example. Just type a date (there's only a few in the list) in Cell B1 - The list will filter when you update the cell

megatronixs · Jun 2nd 2016

Re: passing calendar date into a filter criteria

Hi Cytop,

That is really nice of you this was a really great help

I used this code for the calendar:

Code

Private Sub Calendar1_Click()
Sheets("Main").Range("I3").Value = Format(Me.Calendar1.Value, "dd/mm/yyyy")
End Sub

and changed it a little bit the main code for the filter:

Code

Sub Cases_Received()
    Dim Target As Date
    Dim mySubtotal As Double
    Target = Worksheets("Main").Range("$I$3")
    Sheets("Raw_Data").Activate
    With Sheets("Raw_Data")
        .AutoFilterMode = False
    Rows("1:1").Select
    Selection.AutoFilter
    Worksheets("Raw_Data").Range("$A$1:$DB$56000").AutoFilter Field:=38, Criteria1:=">=" & Format(Target, "yyyy-mm-dd"), _
                                     Operator:=xlAnd, Criteria2:="<" & Format(Target + 1, "yyyy-mm-dd")
       
            End With
                mySubtotal = Application.WorksheetFunction.Subtotal(3, Worksheets("Raw_Data").Range("A2:A56000"))
        Worksheets("Snap_Shot").Range("B3").Value = mySubtotal
        
     Cases_not_started
        
End Sub

Display More

I hope some one can make good use of it

Greetings.

**cytop** · Jun 2nd 2016

Re: passing calendar date into a filter criteria

Thanks for that.

Just one small point.

You use a 'With' Block

Code

With Sheets("Raw_Data") 
        .AutoFilterMode = False 
        Rows("1:1").Select 
        Selection.AutoFilter 
        Worksheets("Raw_Data").Range("$A$1:$DB$56000").AutoFilter Field:=38, Criteria1:=">=" & Format(Target, "yyyy-mm-dd"), _ 
        Operator:=xlAnd, Criteria2:="<" & Format(Target + 1, "yyyy-mm-dd") 
         
    End With

The first 2 lines are fine. The 3rd & 5th lines could be improved.

You are not referencing Row 1 of the worksheet defined in the 'With' statement, but Row 1 of the active sheet. There's no problem here as the sheet was activated before the 'With' statement, but it is a very common mistake and can be dangerous.

For example, you have 2 worksheets. Sheet1 is active and you want to format some stuff then remove everything from sheet2.

Code

Sheet1.Rows("1:1").Select
   Selection.Font.Bold = True


   With Sheet2
       Cells.Delete
   End With

Because the line 'Cells.Delete' is not preceded by a dot (to make it refer to sheet2), it actually applies to the active sheet (Sheet1) - so that line deletes everything on Sheet1 - the correct code is

Code

Sheet1.Rows("1:1").Select
   Selection.Font.Bold = True


   With Sheet2
       .Cells.Delete
   End With

Like I said, not really a problem with your code as the sheet you are working on is the active sheet but you should edit the code so it is correct.

Code

With Sheets("Raw_Data") 
        .AutoFilterMode = False 
        .Rows("1:1").Select 
        Selection.AutoFilter 
        .Range("$A$1:$DB$56000").AutoFilter Field:=38, Criteria1:=">=" & Format(Target, "yyyy-mm-dd"), _ 
        Operator:=xlAnd, Criteria2:="<" & Format(Target + 1, "yyyy-mm-dd") 
         
 End With

Selection does not get preceded with a dot as there can only be one selection in the workbook.

megatronixs · Jun 2nd 2016

Re: passing calendar date into a filter criteria

Hi Cytop,

A good one
I will change this in my code.

I use it in a report that has about 200 of those bloks. I wanted to avoid formulas as they are slow and as I don't know them to good, this speed up things and I can change it easier (in my opinion)
Actually, I hava another report that has over 9000 formulas, and I want to try to do someting similar. Maybe a dictionary sheet where all the criteria is stored and then use it to filter those 9000 and put the numbers in the fields

Greetings.

**cytop** · Jun 2nd 2016

Re: passing calendar date into a filter criteria

Out of context, I couldn't say. Something to consider when you get to it.

passing calendar date into a filter criteria

Participate now!

Share