excelvbaloopsdatetype-mismatch

Excel VBA Type-mismatch Date & Range

I am having trouble looping this data and wondering if you could give some advice.

Please help why am I still getting type mismatch.

Type mismatch

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

In excel, I have tried using the function LARGE and SMALL to determine the first time of the day and Last time of the day. However, before I am able to use the function, an error with type mismatch 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

Had tried using the function CDate and Dim my range of date as dim.

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