Autofilter with dates nightmare!

[philfer]

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

[Bob Phillips]

I tend t use

[vba]

With .Range("$A$1:$A$10")
.AutoFilter Field:=1, Criteria1:="=" & Format(FilterDate, .Range("$A$2").NumberFormat)
End With
[/vba]

[Bob Phillips]

I tend t use

[vba]

With .Range("$A$1:$A$10")
.AutoFilter Field:=1, Criteria1:="=" & Format(FilterDate, .Range("$A$2").NumberFormat)
End With
[/vba]