End a while loop in a condition vba

Question

I have a while loop that I cant seem to end when I want it to end.

In this code:

row = 3

While Cells(row, COL_B) <> ""

    If Not Cells(WBSrow, COL_B).Value = Mid(Cells(row, COL_E), 1, 8) Then

            valueup = Cells(row, COL_E)

            With Range("C1:C400")
                Set c = .Find(valueup, LookIn:=xlValues)
                If c Is Nothing Then
                    Msgbox "Error in Column E"
                End If
            End With


    End If

row = row + 1

Wend

I would like to add Wend right after the Msgbox within that If statement to end the loop if the condition is met. As users will input many rows there might be multiple errors of the same kind and the msgbox will then pop up X times.

The problem I have is that I get "Wend without While" error if I put it there. I have done this with For Each loops before, any ideas why it is not working for me here?

Thanks in advance /Jim

sounds like a duplicate of [this question](http://stackoverflow.com/questions/12200834/break-out-of-a-while-wend-loop-in-vba). — luke_t, Jan 14 '16 at 10:19
Youre right Iturner. I couldn't find that before. Was searching everything I thought.. — JimJimL, Jan 14 '16 at 10:29

score 3 · Accepted Answer · answered Jan 14 '16 at 10:26

3

Can you not use a Do While...

For example, this will exit the loop early...

Sub Test()

Dim i As Integer
Do While i < 100
    i = i + 1
    If i = 10 Then Exit Do
Loop

End Sub

answered Jan 14 '16 at 10:26

PaulG

1,051
7
9

I could. Im very new and wasn't aware of the do while loop. Thank you! – JimJimL Jan 14 '16 at 10:29
No problem at all :) – PaulG Jan 14 '16 at 12:46

Ethun_Hunt · Answer 2 · 2016-01-15T03:25:51.363

1

Add Goto statement just after the msgbox. So when the condition is satisfied it will execute goto statement and come out of loop. I think this will help you.

row = 3
While Cells(row, COL_B) <> ""
    If Not Cells(WBSrow, COL_B).Value = Mid(Cells(row, COL_E), 1, 8) Then
            valueup = Cells(row, COL_E)
            With Range("C1:C400")
                Set c = .Find(valueup, LookIn:=xlValues)
                If c Is Nothing Then
                    Msgbox "Error in Column E"
                    ''''''''''''''''''''''''''
                    Goto Alldone
                    ''''''''''''''''''''''''''
                End If
            End With
    End If
row = row + 1
Wend
''''''''''''''''''''''''''''''''''''''''''''''
AllDone:
''''''''''''''''''''''''''''''''''''''''''''''

edited Jan 15 '16 at 03:25

answered Jan 14 '16 at 10:27

Ethun_Hunt

267
3
16

Ive changed it to a do while loop. This looks like a nice solution only the Sub will be a big larger than just this with more loops. So I think Ill change them all to do while loops instead. Thanks for the tip though! – JimJimL Jan 14 '16 at 10:31
1

Your AllDone line label is inside the while..wend block. That won't end the while..wend loop, it will only skip the remaining lines for the current iteration of the loop. – ThunderFrame Jan 14 '16 at 21:32
Thanx @ThunderFrame, Now it should serve the purpose... (Alldone line label is moved out of the while...wend loop) – Ethun_Hunt Jan 15 '16 at 03:28

score 0 · Answer 3 · answered Jan 14 '16 at 10:32

The While..Wend construct doesn't have an exit capability, but the Do While..Loop clause does:

row = 3

'Add a Do here
Do While Cells(row, COL_B) <> ""

If Not Cells(WBSrow, COL_B).Value = Mid(Cells(row, COL_E), 1, 8) Then

        valueup = Cells(row, COL_E)

        With Range("C1:C400")
            Set c = .Find(valueup, LookIn:=xlValues)
            If c Is Nothing Then
                Msgbox "Error in Column E"
               'Add an Exit Do here
               Exit Do
            End If
        End With
End If

row = row + 1
'Change Wend to Loop here
Loop

End a while loop in a condition vba

3 Answers3