none
regional settings/ date value filtering RRS feed

  • Question

  • Hello,

    Can somebody help me to solve an issue with regional settings?

    I have made a macro to read text file to the spread sheet, then filter data depending on criteria and represent results graphically.

    Macro and filtering was working perfectly with my local regional settings (Denmark), but when I tried my code with Turkish regional settings, code failed and did not filter data according to my criteria. The difference is date representation (in Denmark it is 02-02-2016, while in Turkey 02.02.2016

    I have made a userform with databox for customer to type in the criteria for filtering (in this case a start date and the end date). Date values in column B are filtered.

    Any suggestion where I should look at to make the code work with Turkish regional settings?

    Private Sub DataFilter()
    
        Dim LastRow As Integer
        Dim LastCol As Integer
        Dim StartPeriod As Date
        Dim starttimestr As String
        Dim EndPeriod As Date
        Dim endtimestr As String
        Dim StartDate As Date
        Dim StartTime As String
        Dim EndDate As Date
        Dim EndTime As String
    
        On Error GoTo ErrHandler
        
        With Sheets("Sheet1")
            If .Cells.AutoFilter Then
               .Cells.AutoFilter
            End If
            
            LastRow = .Range("A" & Rows.Count).End(xlUp).Row
            LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
            StartDate = StartDateBox.Value
            StartTime = StartTimeBox.Value
            EndDate = EndDateBox.Value
            EndTime = EndTimeBox.Value
    
            
            StartPeriod = DateValue(StartDate) + TimeValue(StartTime)
            starttimestr = Format(StartPeriod, "dd.mm.yyyy hh:mm")
            EndPeriod = DateValue(EndDate) + TimeValue(EndTime)
            endtimestr = Format(EndPeriod, "dd.mm.yyyy hh:mm")
    
            .Range(.Range("A1"), .Cells(LastRow, LastCol)).AutoFilter _
               Field:=2, _
               Criteria1:=">=" & starttimestr, _
               Operator:=xlAnd, _
               Criteria2:="<=" & endtimestr
        End With
        
        Call PuChartTreat
        Call ProductionChart
        Call UpdateChart
        Call ProductionUpdate
        
    Exit Sub
    ErrHandler:
        MsgBox ("Please enter the correct time!")
        ListBox1.Visible = False
        
    End Sub

    Tuesday, February 2, 2016 8:47 PM

All replies