philfer
10-12-2010, 10:35 AM
Hi,
I am trying to use Autofilter with dates and have got it working almost.
I am using the code below on a form which has a textbox where the user enters a date and a button called filter which filters a row with dates in it in column A of sheet 1 which works after formatting the date to mm/dd/yyyy except when the day or month is a single figure i.e. before the 10th day or before October.
Option Explicit
Private Sub cmdFilter_Click()
Dim FilterDate As Date
FilterDate = CDate(txtDate.Value)
With Worksheets("Sheet1")
.Rows("1:1").AutoFilter
With .Range("$A$1:$A$10")
.AutoFilter Field:=1, Criteria1:="=" & Format(FilterDate, "mm/dd/yyyy")
End With
End With
Unload Me
End Sub
I have tried splitting it, adding a zero and taking the right 2 digits using dateserial trying to make the date into a number
Can anyone help
Cheers in advance
Phil
I am trying to use Autofilter with dates and have got it working almost.
I am using the code below on a form which has a textbox where the user enters a date and a button called filter which filters a row with dates in it in column A of sheet 1 which works after formatting the date to mm/dd/yyyy except when the day or month is a single figure i.e. before the 10th day or before October.
Option Explicit
Private Sub cmdFilter_Click()
Dim FilterDate As Date
FilterDate = CDate(txtDate.Value)
With Worksheets("Sheet1")
.Rows("1:1").AutoFilter
With .Range("$A$1:$A$10")
.AutoFilter Field:=1, Criteria1:="=" & Format(FilterDate, "mm/dd/yyyy")
End With
End With
Unload Me
End Sub
I have tried splitting it, adding a zero and taking the right 2 digits using dateserial trying to make the date into a number
Can anyone help
Cheers in advance
Phil