Excel VBA - InputBox and Autofilter UK date format issue

Question

I am applying an auto filter with specific date ranges using VBA and run the below code. The issue is, each time when I apply the auto filter, date is in the US format (MM/DD/YYYY). On my report, the dates are in the UK format (DD/MM/YYYY) and I need to use this format for my reporting. My code is as below:

Dim sdt As Date
Dim edt As Date
sdt = CDate(Application.InputBox("Choose Start date.", Type:=2))    
edt = CDate(Application.InputBox("Choose End date.", Type:=2))
ActiveSheet.Range("$A:$C").AutoFilter Field:=3, Criteria1:=">=" & sdt, 
Operator:=xlAnd, Criteria2:="<=" & edt

I tried to modify my code slightly but without success:

ActiveSheet.Range("$A:$C").AutoFilter Field:=2, Criteria1:=">=" & CLng(Range("sdt").Value), Criteria2:="<=" & CLng(Range("edt").Value)

or

ActiveSheet.Range("$A:$C").AutoFilter Field:=2, Criteria1:=">=" & CDbl(sdt) Operator:=xlAnd, Criteria2:="<=" & CDbl(edt)

Can you please advise hot to modify my code to apply an auto filter in the UK date format (DD/MM/YYYY)?

Thanks in advance.

PonderingPanda · Accepted Answer · 2019-04-18 12:34:21Z

2

Have you tried applying the String type to std and etd?

Dim sdt As String
Dim edt As String
sdt = Format(CDate(Application.InputBox("Choose Start date.", Default:="31-12-2018", Type:=2)), "MM-DD-YYYY")
edt = Format(CDate(Application.InputBox("Choose End date.", Default:="31-12-2019", Type:=2)), "MM-DD-YYYY")
ActiveSheet.Range("$A:$C").AutoFilter Field:=3, Criteria1:=">=" & sdt, Operator:=xlAnd, Criteria2:="<=" & edt

I have put in a default date as an example, and used the Format date as Error 1004 suggested (but with "MM-DD-YYYY" instead of "DD/MM/YYYY")

answered Apr 18, 2019 at 12:34

PonderingPanda

1241 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Error 1004 · Accepted Answer · 2019-04-18 12:28:47Z

0

You could try:

Option Explicit

Sub test()

    Dim sdt As Date
    Dim edt As Date

    sdt = Format(Application.InputBox("Choose Start date.", Type:=2), "DD/MM/YYYY")
    edt = Format(Application.InputBox("Choose End date.", Type:=2), "DD/MM/YYYY")


    ActiveSheet.Range("$A:$C").AutoFilter Field:=3, Criteria1:=">=" & sdt, Operator:=xlAnd, Criteria2:="<=" & edt

End Sub

edited Apr 18, 2019 at 12:28

answered Apr 18, 2019 at 12:10

Error 1004

8,2863 gold badges36 silver badges58 bronze badges

2 Comments

SeT Over a year ago

I tried your suggestion but this is still applying the date filter in the US format.

SeT Over a year ago

I tried the updated code but the date format is still US.

Collectives™ on Stack Overflow

Excel VBA - InputBox and Autofilter UK date format issue

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related