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]

Did you clear the results tab before you ran it again as it builds the results up each time it is run?
You may need to add a lne of code in to clear the results tab each time so it does not append the results each time.

I ran that date range on the attached and seemed to get the correct result.
Rolsu.xlsm

Here is the code with the clear line added:

Sub test()
    Dim lastrow As Long, erow As Long, i As Long
    Dim mydate As Date, sdate As Date, edate As Date
    
    Sheets("Result").Range("A2:D" & Rows.Count).ClearContents
        
    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(Replace(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

 

[Akuini]

@Rolsu
Data in col C is actually a text instead of date.
You can check it with Year function, it returns #VALUE!

Book2
	A	B	C	D	E
1	CWS code	Work type	Date	Hours
2	404538	Meeting	05.09.2023	1	#VALUE!
Sheet2

Cell Formulas
Range	Formula
E2	E2	=YEAR(C2)

 

[Rolsu]

Did you clear the results tab before you ran it again as it builds the results up each time it is run?
You may need to add a lne of code in to clear the results tab each time so it does not append the results each time.

I ran that date range on the attached and seemed to get the correct result.
Rolsu.xlsm

Here is the code with the clear line added:

Sub test()
    Dim lastrow As Long, erow As Long, i As Long
    Dim mydate As Date, sdate As Date, edate As Date
  
    Sheets("Result").Range("A2:D" & Rows.Count).ClearContents
      
    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(Replace(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

Not just cleared the earlier results but deleted the entire rows. Just in case...
But the probéem still appeares. I don't know what to do. It works only when I do the changes mentioned in my reply. I know this is because of the date formats...

 

[Rolsu]

@Rolsu
Data in col C is actually a text instead of date.
You can check it with Year function, it returns #VALUE!

Book2
	A	B	C	D	E
1	CWS code	Work type	Date	Hours
2	404538	Meeting	05.09.2023	1	#VALUE!
Sheet2

Cell Formulas
Range	Formula
E2	E2	=YEAR(C2)

Hello Akuini!
Indeed. But how? I set the format with excel. If I select the cell it shows "Date" in the format selection window.

 

[Georgiboy]

I had thought you wanted to work with them as text, a lot of the problem is due to the . instead of /

 

[Akuini]

Indeed. But how? I set the format with excel. If I select the cell it shows "Date" in the format selection window.

Try this:
It will format C2:C10000 to "dd.mm.yyyy" & convert the text to date.
Note: even with "dd.mm.yyyy" format, to input date you still need to use "/" or "-" instead of dot, but the cell will show "dd.mm.yyyy".

Sub change_to_date()
Dim tx As String
Dim dt As Date
Dim c As Range
   Range("C2:C10000").NumberFormat = "dd.mm.yyyy"
   With Range("C2", Cells(Rows.Count, "C").End(xlUp))
        For Each c In .Cells
            tx = c.Text
            a = Split(tx, ".")
            c = DateSerial(a(2), a(1), a(0))
        Next
   End With
End Sub

 

[Akuini]

After running the code in post #16, you can check the cell format in Custom category as "dd.mm.yyyy".

 

[Rolsu]

After running the code in post #16, you can check the cell format in Custom category as "dd.mm.yyyy".
View attachment 99455

Hello Akuini!
Sorry for the late reply. It works, thank you.
One question: In the format cell dialog window my format is different. It shows dd\.mm\.yyyy. Is this equal to the one on your picture?

 

[Akuini]

One question: In the format cell dialog window my format is different. It shows dd\.mm\.yyyy. Is this equal to the one on your picture?

Sorry, I'm not sure why you got dd\.mm\.yyyy format by running my code.

 

[Georgiboy]

When you click 'Custom' you can type in the 'Type:' box

In the 'Type:' box just put: dd.mm.yyyy

You don't have to find the specific format in the list.