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

erikarad

New Member
Joined
Dec 2, 2021
Messages
2
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
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:

VBA Code:
.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 :)
Screenshot 2021-12-02 at 18.15.16.png


VBA Code:
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Try changing the line to:
VBA Code:
.AutoFilter field:=3, Criteria1:=">=" & CLng(startDate), Operator:=xlAnd, Criteria2:="<=" & CLng(endDate)
 
Upvote 0
Solution
Try changing the line to:
VBA Code:
.AutoFilter field:=3, Criteria1:=">=" & CLng(startDate), Operator:=xlAnd, Criteria2:="<=" & CLng(endDate)
Thank you so much. This solved the issue :)
 
Upvote 0

Forum statistics

Threads
1,212,934
Messages
6,110,760
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top