CODEWITHSUNDEEP
vbaexceldate
Kamel
Kamel

Reputation: 243

VBA Date format

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:

enter image description here

And this is what I want exactly,

but in the excel file this what I get:

enter image description here

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

Any idea how to resolve this.

Upvotes: 0

Views: 187

Answers (1)

Rory
Rory

Reputation: 34035

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.

Upvotes: 1

Related Questions