Excel Date Issue with AutoFilter 1

[StevePB]

Excel 2016 on Windows 10.

I have some code which filters the data in a worksheet by date. The date is entered into a user form and populates a date variable. The date format is: dd/mm/yyyy

When I activate the autofilter in the code, it reads the date as US format and therefore does not display any data.

I have checked that all relevant regional settings in MS Office and Windows are set to UK English with the appropriate date formats.

This is the code that assigns the date variable:

'=====

Dim ReportDate As Date
ReportDate = TextBox1.Value

'=====

The user enters something like: 07/11/2017 (for 7th November 2017)

This is the code that activates the autofilter:

'=====

For i = 1 To 24
        With ActiveSheet.Range("A1:" & LastData)
            .AutoFilter Field:=1, Criteria1:=ReportDate
            .AutoFilter Field:=2, Criteria1:=i
        End With
' Do some other stuff...
Next i

'=====

If I break into the code just after the autofilter is activated and check the filter properties in the worksheet it shows a date filter value of: 11/7/2017

I would be grateful for any ideas you may have to resolve this.

 

[SkipVought]

Hi,

Dim ReportDate As [b]String[/b]
ReportDate = TextBox1.Value
'...
For i = 1 To 24
        With ActiveSheet.Range("A1:" & LastData)
            .AutoFilter Field:=1, Criteria1:= [b]"=" &[/b] ReportDate
            .AutoFilter Field:=2, Criteria1:=i
        End With
' Do some other stuff...
Next i

Hint: record a macro whilst setting a filter and observe the result, as I did.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[StevePB]

Thanks Skip - it works perfectly.

It was actually someone else's code that I needed to fix.

I haven't posted here for a long time - good to see that you are still around!

Thanks again - StevePB.

 

[SkipVought]

6 years. Welcome back!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]