Issues filtering dates with VBA autofilter on computers with German regional setting

[erikarad]

I am trying to filter out specific dates in a table using the autofilter function with the AND operator. The code below works perfectly well for users who have their regional settings set to those recommended by windows (English United States). But, for users who have their computers set to German the autofilter returns nothing.

It seems the error happens in the following line from the code snippet below:

.AutoFilter field:=3, Criteria1:=">=" & startDate, Operator:=xlAnd, Criteria2:="<=" & endDate

I checked if the dates were input in the filter and if they were formatted correctly. That doesn't seem to be the issue. The dates appear only once I have clicked "ok" in the custom autofilter box. Is there any way I can solve this? Thanks

For t = 1 To timeline
            ''update forecast dates
            Dim startDate As Date, endDate As Date

            'for first month only consider second half of month
            If t = 1 Then
                startDate = "15/" & Month(Now) & "/" & Year(Now)
            Else
                startDate = DateSerial(Year(Now), Month(Now) + t - 1, 1) ' t-1 otherwise the forecast will skip a month
            End If

            endDate = DateSerial(Year(Now), Month(Now) + t, 0)               

            'filter out relevant data
            With overview.Range("C10")
                .AutoFilter field:=5, Criteria1:="Direct"
                .AutoFilter field:=3, Criteria1:=">=" & startDate, Operator:=xlAnd, Criteria2:="<=" & endDate
            End With

            ''put values into liquidity cash in out sheet
            'cash in movement

            liquidity.Cells(inputRow, 6 + t).Value2 = overview.Range("O7").Value2 'value input starts in column 7

            'Drawdowns

            liquidity.Cells((inputRow + 1), 6 + t).Value2 = overview.Range("R7").Value2 'value input starts in column 7
        Next t

 

[John_w]

Try changing the line to:

.AutoFilter field:=3, Criteria1:=">=" & CLng(startDate), Operator:=xlAnd, Criteria2:="<=" & CLng(endDate)

 

[erikarad]

Try changing the line to:

.AutoFilter field:=3, Criteria1:=">=" & CLng(startDate), Operator:=xlAnd, Criteria2:="<=" & CLng(endDate)

Thank you so much. This solved the issue