VBA code variable keeps reverting to MMDDYYYY

[MrCranky]

Hi All

I have been getting some code together which looks at a data tab;e and extracts between 2 date ranges.

My issue is the data in the rawdata is in DDMMYYYY - when the msgbox pops up and dates are entered the search results revert the dates its searching for to MMDDYYYY.

Any ideas on how i can force a DDMMYYYY?

code for reference

Sub Daterangegrap()


'Disabling screen updates
'Application.ScreenUpdating = False


'Declaring two variables of Date data type
Dim StartDate, EndDate As Date




'Declaring variable for worksheet object
Dim MainWorksheet As Worksheet




'User inputs for date range


On Error Resume Next
    StarDate = Format(Date, "DD/MM/YYYY")
    StartDate = InputBox("Start Date")
     If StartDate = 0 Then Exit Sub
    If Not IsDate(StartDate) Then
       MsgBox "Invalid date"
       Exit Sub
    End If
    
    EndDate = Format(Date, "DD/MM/YYYY")
    EndDate = InputBox("End Date. Must be greater than " & Format(StartDate, "ddd d mmm yyyy"))
    
   If EndDate = 0 Then Exit Sub
    If Not IsDate(StartDate) Then
       MsgBox "Invalid date"
       Exit Sub
    ElseIf StartDate > EndDate Then
        MsgBox "Start date greater then end date"
        Exit Sub
    End If


'Initializing worksheet object with "RawData" worksheet
Set MainWorksheet = Worksheets("RawData")


'Activating the worksheet object
MainWorksheet.Activate


'Sorting the data by date in column A in ascending order
Range("A1").CurrentRegion.Sort _
      key1:=Range("A1"), order1:=xlAscending, _
       Header:=xlYes


'Filter the data based on date range between starting date and end date
Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:= _
        ">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate


'Copy the filter data
ActiveSheet.AutoFilter.Range.Copy


'Inserting new worksheet after the last worksheet in the workbook
Worksheets.Add after:=Worksheets(Worksheets.Count)


'Pasting the copied data
ActiveSheet.Paste


'Auto adjusting the size of selected columns
Selection.Columns.AutoFit


Range("A1").Select
 
'Activating the "RawData" sheet
MainWorksheet.Activate


'Removing filter from the worksheet which we applied earlier
Selection.AutoFilter


Sheets("Macro").Activate




End Sub

 

[Yongle]

Welcome to the forum
- it is easier to read code if you include inside code tags
- click on # icon above post window
[ CODE ] code goes here [ /CODE ]

Excel speaks American. Don't fight it! Here is one way

Amend this line and it should filter correctly for you

'Filter the data based on date range between starting date and end date
Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:= _
            ">=" & [I][COLOR=#ff0000]Format(StartDate, "MM/DD/YYYY")[/COLOR][/I], Operator:=xlAnd, Criteria2:="<=" & [I][COLOR=#ff0000]Format(EndDate, "MM/DD/YYYY")[/COLOR][/I]

Declaring variables : variable type

Regardless of what you see others do ...
- in VBA, specify the type for each variable individually to force the variable to be the type you want
- otherwise it becomes a variant and VBA decides as it goes along! It will often still work correctly. But specifying variable type correctly makes life easier when things go awry. Amend like this.

Dim [COLOR=#ff0000]StartDate As Date[/COLOR], EndDate As Date

Run this to see for yourself

Sub VariableType()
    Dim StartDate, EndDate As Date
    MsgBox "StartDate type: " & vbTab & VarType(StartDate) & vbCr & "EndDate   type:" & vbTab & VarType(EndDate)
End Sub

 

[S_W_Langdon]

Hi Welcome to the Forum,

I have been having a similar issue as of late and posted a similar question,
A few people got back to me, and it seems to be working fine now, below is a link to my original post hopefully the answers on here can resolve your question

https://www.mrexcel.com/forum/excel...pull-data-ms-access-depending-date-range.html

 

[MrCranky]

This worked perfectly thanks - wow that was doing my head in!

Welcome to the forum
- it is easier to read code if you include inside code tags
- click on # icon above post window
[ CODE ] code goes here [ /CODE ]

Excel speaks American. Don't fight it! Here is one way

Amend this line and it should filter correctly for you

'Filter the data based on date range between starting date and end date
Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:= _
            ">=" & [I][COLOR=#ff0000]Format(StartDate, "MM/DD/YYYY")[/COLOR][/I], Operator:=xlAnd, Criteria2:="<=" & [I][COLOR=#ff0000]Format(EndDate, "MM/DD/YYYY")[/COLOR][/I]

Declaring variables : variable type

Regardless of what you see others do ...
- in VBA, specify the type for each variable individually to force the variable to be the type you want
- otherwise it becomes a variant and VBA decides as it goes along! It will often still work correctly. But specifying variable type correctly makes life easier when things go awry. Amend like this.

Dim [COLOR=#ff0000]StartDate As Date[/COLOR], EndDate As Date

Run this to see for yourself

Sub VariableType()
    Dim StartDate, EndDate As Date
    MsgBox "StartDate type: " & vbTab & VarType(StartDate) & vbCr & "EndDate   type:" & vbTab & VarType(EndDate)
End Sub

 

[Yongle]

thanks for your feedback