VBA Date format

Question

I need to filter a column that contain dates. I need to get all rows where the date is between the current date and the current date + 15 days

This is my VBA code to do that:

    Dim seuilDate As Date, currentDate As Date
    seuilDate = DateAdd("d", 15, Date)
    currentDate = Date

    MsgBox "Date seuil :" & seuilDate & "  Current Date:" & currentDate


    With Workbooks("macro1.xlsm").Worksheets("Feuil1")
        If .FilterMode Then .ShowAllData
        '.range("C:C").AutoFilter Field:=3, Operator:=xlFilterValues
        .range("C:C").AutoFilter Field:=1, Criteria1:=">=" & currentDate, Criteria2:="<" & seuilDate, Operator:=xlAnd
    End With

this is what shows the MessageBox:

And this is what I want exactly,

but in the excel file this what I get:

the day and the month fields are inversed in the CurrentDate+15 variable.

Any idea how to resolve this.

Rory · Accepted Answer · 2016-04-25 12:25:11Z

1

Convert the date values to Doubles when applying them to the autofilter:

   .range("C:C").AutoFilter Field:=1, Criteria1:=">=" & CDbl(currentDate), Criteria2:="<" & CDbl(seuilDate), Operator:=xlAnd

as VBA is very US-centric.

answered Apr 25, 2016 at 12:25

Rory

34.2k5 gold badges40 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

VBA Date format

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related