Dfyjkt

I have this Do While loop:

Sub TEST_LOOP()
Dim i As Integer
i = 2

Do While Cells(i, 3) <> "" And _
Int(Mid(Cells(i, 3), 12, 2)) = 21
 Value = Value + Cells(i, 4)
 i = i + 1
Loop

End Sub

Which is applied to this basic data set:

I get an error message:

"Run-time error '13': Type mismatch.

The problem happens because when the loop reaches the first empty cell (i = 7)
then the Int function is applied to an empty value Mid(Cells(i, 3), 12, 2 gives nothing). So I was wondering whether there was an efficient way to exit the loop once the cell in question does not meet the two conditions, that is it is not empty and it involves the hour 21.

You need to check if the cell value is a date first, then proceed. See if this helps:

Sub TEST_LOOP()

 Dim i As Long, ws As Worksheet
 Set ws = ActiveSheet

 For i = 2 To lastRow(ws, "C")
 If isDate(ws.Cells(i, 3)) Then

 If Int(Mid(ws.Cells(i, 3), 12, 2)) = 21 Then

 Value = Value + ws.Cells(i, 4)

 End If

 End If
 Next i

End Sub

Function lastRow(ws As Worksheet, Optional col As Variant = 1) As Long
 With ws
 lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
 End With
End Function

The issue is that in an IF() or Do While statement, everything is evaluated - even if a previous statement returns FALSE. So you will need to first check if your cell is a date before trying to proceed.