Do While on Two Conditions

Question

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.

Unfortunately VBA doesn't have `continue` or `break` statements. See https://stackoverflow.com/a/12960972 for a solution. — Robert Harvey, Nov 08 '18 at 20:14

K.Dᴀᴠɪs · Accepted Answer · 2018-11-08T20:29:27.710

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.

Do While on Two Conditions

1 Answers1