Search code examples
excelvbaloopsdatetype-mismatch

Extract the first and last entries for each day

My purpose is to extract the first and last entries for each day.
Type mismatch

In Excel, I tried functions LARGE and SMALL to determine the first time of the day and last time of the day.
Before I am able to use the function, an error occurs.

Run-time error '13'
Type mismatch

Example: for 1 Aug 2024, only 7:27am and 5:38pm then follow by 2 Aug 2024, 7:34 and 05:44pm

Tried the function CDate and Dim my range as date.

Solution

  • You're attempting to load an array of cell values into a date variable.

    Loading them into an array held in a variant means the entire process can be significantly faster than reading each cell's value. When testing, I found 1m rows takes around 1/10th of a second, vs 1 whole second if scanning cell values.

    Try this:

    Sub testing2()

    Dim dateList As Variant
    Dim firstDate As Date
    newLR = Sheet5.Cells(Rows.Count, 1).End(xlUp).Row
    
    firstDate = #1/8/2024#
    
    dateList = Sheet5.Range("a1:a" & newLR).Value
    
    For x = 1 To newLR
        If firstDate = dateList(x, 1) Then
            Debug.Print "Good record at row " & x
        End If
    Next x

End Sub