VBA How to stop the While loop

Question

I can't stop the second loop. It just keeps incrementing. How do I stop a While Loop in VBA?

While Sheets("Temp BU v2").Cells(1, iColCountTempList) <> ""
    If sChooseTemp = Sheets("Temp BU v2").Cells(1, iColCountTempList) Then
        MsgBox "WHOOHOO!"
        While Sheets("Temp BU v2").Cells(iRow, iColCountTempList) <> ""
            If sChooseTempCont = iRow Then
                MsgBox "YEAH!"
                sFinalMail = Sheets("Temp BU v2").Cells(iRow, iColCountTempList)
            Else
                iRow = iRow + 1
            End If
        Wend
    Else
        iColCountTempList = iColCountTempList + 1
    End If
Wend

possible duplicate of [Break out of a While...Wend loop in VBA](http://stackoverflow.com/questions/12200834/break-out-of-a-while-wend-loop-in-vba) — GSerg, Jul 15 '15 at 08:20

score 0 · Answer 1 · answered Jul 15 '15 at 15:49

As far as I can see, you're not resetting your variables you're using within your loop

Without more of the code, it's a bit difficult to say what exactly is the issue but try the following...

While Sheets("Temp BU v2").Cells(1, iColCountTempList) <> ""
    If sChooseTemp = Sheets("Temp BU v2").Cells(1, iColCountTempList) Then
        MsgBox "WHOOHOO!"
        iRow = 1
        While Sheets("Temp BU v2").Cells(iRow, iColCountTempList) <> ""
            If sChooseTempCont = iRow Then
                MsgBox "YEAH!"
                sFinalMail = Sheets("Temp BU v2").Cells(iRow, iColCountTempList)
            Else
                iRow = iRow + 1
            End If
        Wend
    Else
        iColCountTempList = iColCountTempList + 1
    End If
Wend

On a separate note; this isn't a great technique for matching. You may want to consider using a .find function instead or a match/index solution. Also using Do loops is normally preferable to While loops

VBA How to stop the While loop

1 Answers1