View original (French)

Filtering dates by VBA macro - format issue

Solved
Sailorbop -  
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
      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
    1. 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
    2. 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
    4. sailorbop
      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
      1. sailorbop
        Re-Hello!!
        Anyone who might have a little idea?? Thanks in advance!!!
        0
        1. 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
          1. 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
            1. 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