VBA loop through date type mismatch

[Rolsu]

Hi all!

I have a problem with the dates.
I have a list of dates that I want to loop through and copy the result to another sheet. The condition is a date range.
Here is my code:

Sub CommandButton1_Click()

    Dim lastrow, erow, i As Long
    Dim mydate, sdate, edate As Date
        
        sdate = InputBox("", "start date")
        edate = InputBox("", "end date")
        
        lastrow = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
        
        Sheets("Data").Range("A1").Select
        
            For i = 2 To lastrow 'using headers
                mydate = Cells(i, 3)
                If mydate >= sdate And mydate <= edate Then
                erow = Sheets("Result").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                Range(Cells(i, 1), Cells(i, 4)).Copy Destination:=Sheets("Result").Cells(erow, 1)
                End If
            Next i
End Sub

I'd like to use the format "dd.mm.yyyy" everywhere. The list of dates are already set to this format. But when I enter this format to the inputboxes it gives a run-time error '13" type mismatch to the line where I set the "edate"
I've tried to format the inputbox value to the same ( Dim sdate, edate as String, Inputbox("", "start date", Format(Now(), "dd.mm.yyyy")) ), but in that case the error came to the "mydate" line.
I don't know how to set them so they match.
Can someone help?
Thank you.

 

[Georgiboy]

When you define variables as below:

Dim mydate, sdate, edate As Date

Only edate has been defined as a specific type, the first two are left defined with no type, this is why the first error you saw was on 'edate'.
sdate did not give an error as it never had a type, sdate would have changed to a string and accepted any value from the input box.

Input box returns a string. The error you see with edate is because it was defined as a date but was passed a string so you get the error.

IMO, this is the importance of defining variables correctly, it allows you to get the correct errors and speeds up debugging.

You could use a replace function to change the . into / and wrap that in CDATE to ensure you get a date out of the IBox.

There are other ways to write this but thought i would leave the code intact so you can see what has been changed:

Sub test()
    Dim lastrow As Long, erow As Long, i As Long
    Dim mydate As Date, sdate As Date, edate As Date
        
    sdate = CDate(Replace(InputBox("", "start date"), ".", "/"))
    edate = CDate(Replace(InputBox("", "end date"), ".", "/"))
    lastrow = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To lastrow 'using headers
        mydate = CDate(Cells(i, 3).Value)
        If mydate >= sdate And mydate <= edate Then
        erow = Sheets("Result").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        Range(Cells(i, 1), Cells(i, 4)).Copy Destination:=Sheets("Result").Cells(erow, 1)
        End If
    Next i
End Sub

 

[Rolsu]

When you define variables as below:

Dim mydate, sdate, edate As Date

Only edate has been defined as a specific type, the first two are left defined with no type, this is why the first error you saw was on 'edate'.
sdate did not give an error as it never had a type, sdate would have changed to a string and accepted any value from the input box.

Input box returns a string. The error you see with edate is because it was defined as a date but was passed a string so you get the error.

IMO, this is the importance of defining variables correctly, it allows you to get the correct errors and speeds up debugging.

You could use a replace function to change the . into / and wrap that in CDATE to ensure you get a date out of the IBox.

There are other ways to write this but thought i would leave the code intact so you can see what has been changed:

Sub test()
    Dim lastrow As Long, erow As Long, i As Long
    Dim mydate As Date, sdate As Date, edate As Date
       
    sdate = CDate(Replace(InputBox("", "start date"), ".", "/"))
    edate = CDate(Replace(InputBox("", "end date"), ".", "/"))
    lastrow = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
   
    For i = 2 To lastrow 'using headers
        mydate = CDate(Cells(i, 3).Value)
        If mydate >= sdate And mydate <= edate Then
        erow = Sheets("Result").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        Range(Cells(i, 1), Cells(i, 4)).Copy Destination:=Sheets("Result").Cells(erow, 1)
        End If
    Next i
End Sub

Hi Georgiboy!

At the first time, when I just copy-paste your code it didn't work. The date of list is set and shows the following format: "dd.mm.yyyy." When I run your code it gives the error to the mydate line again.
But, I've changed the format in every cell to "dd. mm. yyyy" (I put spaces after the dots) manually. And suprisingly, it works...
Seriously, I'm getting crazy with this...
Why it doesn't work when I set the format with the excel built-in function (Format cells-Dates-United Kingdom-14.03.2012), but it works when I manually change the already set format?

 

[Georgiboy]

Hmm, a little strange, have you tried changing this line:

mydate = CDate(Cells(i, 3).Value)

To:

mydate = CDate(Replace(Cells(i, 3).Value, ".", "/"))

 

[Rolsu]

With that change it works fine.
Thank you.

 

[Rolsu]

Hmm, a little strange, have you tried changing this line:

mydate = CDate(Cells(i, 3).Value)

To:

mydate = CDate(Replace(Cells(i, 3).Value, ".", "/"))

Sorry, still not working. In this case the result is the first 6 lines of the date list, mixed the 8th and 9th months data.

 

[Georgiboy]

Maybe you could share the data you are looking at with the XL2BB add-in, I can't see what you are looking at.

 

[Rolsu]

Maybe you could share the data you are looking at with the XL2BB add-in, I can't see what you are looking at.

This is sheet1:

Copy_with_ date_range_loop.xlsb
	A	B	C	D
1	CWS code	Work type	Date	Hours
2	404538	Meeting	05.09.2023	1
3	404538	Design	06.09.2023	2
4	404538	Design	07.09.2023	3
5	408821	Meeting	15.08.2023	1
6	408821	Design	16.08.2023	2
7	408821	Design	17.08.2023	2
8	406733	Meeting	10.09.2023	1
9	406733	Design	11.09.2023	3
10	406733	Design	12.09.2023	4
11	402864	Meeting	25.10.2023	1
12	402864	Design	26.10.2023	5
13	402864	Design	27.10.2023	3
Data

The 2nd sheet only has the same headers.
Thank you.

 

[Georgiboy]

Sorry, still not working. In this case the result is the first 6 lines of the date list, mixed the 8th and 9th months data.

Do you think you could explain this in a little more detail?

I just ran it with the date range of 15/08/2023 to 25/10/2023 and it pulled the correct data for me.

 

[Rolsu]

Do you think you could explain this in a little more detail?

I just ran it with the date range of 15/08/2023 to 25/10/2023 and it pulled the correct data for me.

I launch the inputbox for the sdate. I type 01.09.2023 or 01/09/2023. Than the second is edate in the same format with the last day of the month 30/09/2023. And the result is just the range of data from sheet1, second row to 7th row. So it mixed the August and September.