CODEWITHSUNDEEP
vbaexcel
Janet Yung
Janet Yung

Reputation: 11

Excel VBA: How to exit loop if blank cell found

I'm having trouble finding the solution to run the loop if a blank cell is found. This is the first time I've ever used VBA (but hopefully not the last), so thank you in advance for your help and advice.

Basically, for a column of values ("Units"), I want to copy and paste each value into an input box, copy the results and paste elsewhere, then repeat for next value until the next cell in the "Units" column is blank.

Any advice on how to put in this condition would be greatly appreciated. I've tried a variety of "Do while..." and "If..." etc. but couldn't crack the structure.

Sub ResultsTable()

    Application.ScreenUpdating = False

    Dim UnitRows As Integer
    UnitRows = Range("Units").Rows.Count

    Do While Range("Units").Cells(i, 1).Value <> ""

    i = i + 1

        Range("Units").Cells(i, 1).Copy
        Range("Input").PasteSpecial xlPasteValues

        Range("ABBA").Copy
        Range("ABBAO").Cells(i, 1).PasteSpecial xlPasteValues

        Range("BABBA").Copy
        Range("BABBAO").Cells(i, 1).PasteSpecial xlPasteValues

        Range("CABBA").Copy
        Range("CABBAO").Cells(i, 1).PasteSpecial xlPasteValues

        Range("DABBA").Copy
        Range("DABBAO").Cells(i, 1).PasteSpecial xlPasteValues



    Range("Units").Copy
    Range("UnitsOutput").PasteSpecial xlPasteValues

    Application.ScreenUpdating = True

End Sub

Upvotes: 0

Views: 21674

Answers (1)

newacc2240
newacc2240

Reputation: 1425

You cans use .Value property to check whether it is blank or not like this:

Range("Units").Cells(i, 1).Value <> ""

It returns False when the cell is blank.

I'm not sure that you need the code to skip the loop or to terminate the whole loop if it meets a blank. If you needed only skipping the current loop, try a for-loop with a if statement inside.

For i = 1 To UnitRows
    If Range("Units").Cells(i, 1).Value <> "" Then
        'your code here
    End If
Next i

When the Cells(i, 1) is blank, the marco goes to next i till it reaches UnitRows.

If you want to terminate the loop, try a Do-while loop.

Do While Range("Units").Cells(i, 1).Value <> ""
    'your code here
    i = i + 1
Loop

When Cells(i, 1) is blank, the condition goes to False, hence the loop is stopped.

Remember, always be aware while using both For loop and While loop at the same time, especially you are using the same variable i to control the loops.

Upvotes: 2

Related Questions