INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel Date Issue with AutoFilter

Excel Date Issue with AutoFilter

(OP)
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:

'=====

CODE --> vba

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:

'=====

CODE --> vba

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.

RE: Excel Date Issue with AutoFilter

Hi,

CODE

Dim ReportDate As String
ReportDate = TextBox1.Value
'...
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 

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel Date Issue with AutoFilter

(OP)
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.

RE: Excel Date Issue with AutoFilter

6 years. Welcome back!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close