Geek Answers Handbook

How to end a while loop in VBA

I would like to know what is wrong with my coding, since I cannot finish the do while loop in Microsoft Excel VBA. I want to end this while loop if the next line is empty.

Do While Cells(RowName, 1) <> ""
    Name = Cells(RowName, ColumnName)
    MsgBox Name
    RowName = RowName + 1
Loop

Please enlighten me as I am still a beginner. MsgBox continued to appear and did not end, even if it is empty.

+4
source share
3 answers

"Exit Do" - you can use this command wherever you want to stop the loop.

Sub ExitDoSample
    Dim rowname As Integer
    Dim ColumnName  As Integer
    rowname = 1
    ColumnName = 1


    Do While Cells(RowName, 1) <> ""

        Name = Cells(rowname, ColumnName).Value
        If Name = "" Or IsEmpty(Name) Then
            Exit Do
        Else
            MsgBox Name
        End If
        rowname = rowname + 1
    Loop
End Sub
+2
source

Sankar Balasubramanyan’s answer is very close, but has several problems. This is how I do it. Do it until it is empty, but Exit Do if the cropped value is an empty string.

Sub SampleEnding()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim RowNum As Long: RowNum = 1
    Dim ColNum As Long: ColNum = 3
    Dim Name As String

    Do While Not IsEmpty(ws.Cells(RowNum, 1))
        If Trim(ws.Cells(RowNum, 1).Value) <> "" Then
            Name = ws.Cells(RowNum, ColNum)
            MsgBox Name
        Else
            Exit Do
        End If
        RowNum = RowNum + 1
    Loop

End Sub

RowNum , , Excel .

, . /.

+3

You check if the RowName row in column 1 is empty, but you select a name from the ColumnName column, which may not be column 1. Thus, column 1 may have data in it (so the check goes through and the loop continues), but the column number. The column name may be blank, so your message field shows something blank. Could this be a problem?

0
source

More articles:


All Articles