Exit a while loop in VBA/VBS

Question

I have an Excel VBA program that loops through each row of data in a data sheet.

My goal is to exit the while loop once boolean bFound is set as True.

I think my condition "Or bFound=True" might be incorrect.

bFound = False
While Sheets("Data").Cells(iRow, 1) <> "" Or bFound = True

    If Sheets("Data").Cells(iRow, 11) = Sheets("Data2").Cells(iRow, 1) Then
        bFound = True
    End If

    iRow = iRow + 1
Wend
'exit loop after the boolean=true

possible duplicate of [Exit a while loop in VBS/VBA](http://stackoverflow.com/questions/1271949/exit-a-while-loop-in-vbs-vba) — Selfish, Sep 26 '15 at 21:41

omegastripes · Accepted Answer · 2016-01-04T22:50:09.277

14

Use Do ... Loop and Exit Do

bFound = False
Do While Sheets("Data").Cells(iRow, 1) <> ""
    bFound = Sheets("Data").Cells(iRow, 11) = Sheets("Data2").Cells(iRow, 1)
    If bFound Then Exit Do
    iRow = iRow + 1
Loop

edited Jan 04 '16 at 22:50

answered Sep 26 '15 at 13:03

omegastripes

12,351
4
45
96

@bigbryan, note that outside the loop `iRow` points to the row where exit condition has been matched, but not to the next row as it intended in your code. – omegastripes Sep 26 '15 at 13:13

Selfish · Answer 2 · 2015-09-26T13:08:10.643

Flip the logic around, I expect this will work for you:

bFound = False
While Sheets("Data").Cells(iRow, 1) <> "" And bFound = False

    If Sheets("Data").Cells(iRow, 11) = Sheets("Data2").Cells(iRow, 1) Then
        bFound = True
    End If

    iRow = iRow + 1
Wend

Explanation:

While Sheets("Data").Cells(iRow, 1) <> "" And bFound = False

will allow the loop to carry on only while we still have data to process AND we still haven't changed bFound, which has initial value of False.

Another option is to use the breakable form of While in VBS:

Do While Sheets("Data").Cells(iRow, 1) <> ""

    If Sheets("Data").Cells(iRow, 11) = Sheets("Data2").Cells(iRow, 1) Then Exit Do

    iRow = iRow + 1

Loop

Exit a while loop in VBA/VBS

2 Answers2