View original (French)

Filtering dates by VBA macro - format issue

Solved
Sailorbop -  
 Riaolle -
VBA - Excel
Hello everyone,
(I found some answers but I've already tested them without success)

I would like to create a macro to filter between two dates, whose values are either in 2 cells or in 2 textboxes...

The problem is that even when specifying the format, Excel filters by swapping the month and the day.

For example:
Selection.AutoFilter Field:=1, Criteria1:=">=" & Format(TextBox1, "dd/mm/yy"), Operator:=xlAnd, Criteria2:="<=" & TextBox2

Date entered in textbox1 = 06/02/07 sends the criterion 02/06/2007
Date entered in textbox2 = 10/02/07 sends the criterion 02/10/2007

The worst part is that if I give the criterion:

Criteria1:= Format(TextBox1, "dd/mm/yy"), Operator:=xlOr, Criteria2:=TextBox2

Well, there... it works...
Note that I omitted Format(textbox2...) and there is no difference.

Thank you very much!
Configuration: Windows XP Internet Explorer 6.0

9 answers

  1. Juan's
     
    I struggled for hours to find this!!!

    There's a little trick to make it work.
    You have to format the date in VBA (dd/mm/yyyy) and then convert it to a number via transition TextBoxes.

    13/05/2007 = 39215 in VBA and Excel

    In the example below, I want to filter my column (number 4, so D) between 2 dates:
    - start date = TextBox1 (format dd/mm/yyyy)
    - end date = TextBox2 (format dd/mm/yyyy)

    - start date = TextBox3 (numeric format)
    - end date = TextBox4 (numeric format)

    You need to fill in TextBoxes 1 and 2.
    TextBoxes 3 and 4 are only used as a transition in the UserForm

    Here's the code: 

    '===== BEGIN CHOOSING THE PERIOD 'date range startdate = CDate(Format(TextBox1.Value, "dd/mm/yyyy")) enddate = CDate(Format(TextBox2.Value, "dd/mm/yyyy")) 'NUMERIC TRANSFORMATION OF THE DATE Dim startdate2 As Single startdate2 = startdate TextBox3.Value = startdate2 Dim enddate2 As Single enddate2 = enddate TextBox4.Value = enddate2 'FILTER LAUNCH ============ Selection.AutoFilter Field:=4, Criteria1:=">=" & TextBox3.Value, Operator:=xlAnd, Criteria2:="<=" & TextBox4.Value '+++++ END CHOICE OF THE PERIOD


    It's a bit far-fetched... But it works!!!

    ++
    Juan's
    15
    1. redaiwa Posted messages 370 Status Member 119
       
      Hello Juan's.
      It's a shame that I can only give you one point because you deserve at least 10!!!
      You certainly helped "Sailorbop", but in reality, you continue to help thousands of people even today... including me :)
      "The good word is like a beautiful tree whose root is firm and whose branches rise into the sky, bearing fruit at every moment" (Quran)
      0
    2. Jeanno
       
      Thank you very much!!!
      0
    3. qui qu'ou
       
      You're welcome!
      0
  2. david_wklw
     
    To use a date, personally I do it like this and I'm not bothered

    DateForTheFilter = format(ADate, "#")
    8
  3. UnknownVBA
     
    The answer here for English speakers:
    http://www.ozgrid.com/VBA/autofilter-vba-dates.htm

    For those who dislike English:
    The problem arises due to the non-American date format. To remedy this, you need to transform the date into a "long" variable using the "DateSerial" function.

    The code below: 

    Dim dDate As Date Dim lDate As Long dDate = DateSerial(Year(dDate), Month(dDate), Day(dDate)) lDate = dDate Range("A1").AutoFilter Field:=1 Criteria1:=">" & lDate


    Enjoy
    3
    1. Riaolle
       
      It's perfect!
      0
  4. Papou
     
    PS: The CDate function converts dates to the default format of Windows. It is therefore unnecessary to apply the formatting "dd/mm/yy".

    Your code would be: 

    Selection.AutoFilter Field:=1, Criteria1:=">=" & CDate(TextBox1.Value), Operator:=xlAnd, Criteria2:="<=" & CDate(TextBox2.Value)


    Papou
    1
    1. Juan's
       
      Yes, but the filter doesn't work in this case.
      The date needs to be converted to a number as indicated above.
      0
  6. sailorbop Posted messages 2 Status Member 1
     
    JUAN'S
    With your code, I found that it also works like this (a bit simpler):
    => In fact, I'm not using TextBox3 and 4 as intermediaries and I'm filtering directly in the date column (I didn't fully understand why it works because I'm not great at VBA, but anyway...) :

    Private Sub CommandButton1_Click()

    Dim a As Single
    Dim b As Single

    a = CDate(TextBox1)
    b = CDate(TextBox2)

    Selection.AutoFilter Field:=1, Criteria1:=">=" & a, Operator:=xlAnd, Criteria2:="<=" & b

    End Sub

    (Note it crashes if nothing is entered in a textbox but it’s just a small thing to add)

    Thanks again!
    1
  7. sailorbop Posted messages 2 Status Member 1
     
    Re-Hello!!
    Anyone who might have a little idea?? Thanks in advance!!!
    0
  8. Papou
     
    Good evening sailorbop,

    To retrieve texts in the form of a date, simply convert the contents as follows: 

    CDate(TextBox1.Value)


    Best regards.

    Papou
    0
  9. Sailor
     
    Thank you very much!
    So, strangely for those who have tested it: one date works, but two dates do not work unless... for dates on 01/01/200xx... (January 1st of the selected years).

    I really think it's a translation issue US => FR.

    Thanks again! And sorry for the headache :o)!!
    0
  10. Victor
     
    Hello,

    I am currently compiling a small macro for work in Excel 2007.
    For this auto filter macro, the last line of my base file is always ignored; do you have a solution to make sure it is considered?
    0