Filtering dates by VBA macro - format issue
Solved
Sailorbop
-
Riaolle -
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!
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 réponses
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:
It's a bit far-fetched... But it works!!!
++
Juan's
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
To use a date, personally I do it like this and I'm not bothered
DateForTheFilter = format(ADate, "#")
DateForTheFilter = format(ADate, "#")
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:
Enjoy
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
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!
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!
Good evening sailorbop,
To retrieve texts in the form of a date, simply convert the contents as follows:
Best regards.
Papou
To retrieve texts in the form of a date, simply convert the contents as follows:
CDate(TextBox1.Value)
Best regards.
Papou
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)