How to Use Loop in VBA in Excel: Simple Guide

In VBA for Excel, you use For, While, or Do loops to repeat actions multiple times. Loops help automate tasks like processing rows or cells by running code repeatedly until a condition is met or a set number of times.

Syntax

Here are common loop types in VBA:

• For Loop: Repeats code a fixed number of times.
• While Loop: Repeats code while a condition is true.
• Do Loop: Repeats code until a condition is met.

Each loop has a start, a condition, and an end.

vba

For i = 1 To 10
    ' Code to repeat
Next i

While condition
    ' Code to repeat
Wend

Do While condition
    ' Code to repeat
Loop

Example

This example uses a For loop to fill cells A1 to A5 with numbers 1 to 5.

vba

Sub FillNumbers()
    Dim i As Integer
    For i = 1 To 5
        Cells(i, 1).Value = i
    Next i
End Sub

Output

Cells A1=1, A2=2, A3=3, A4=4, A5=5

Common Pitfalls

Common mistakes include:

• Forgetting Next to close a For loop.
• Using a wrong loop condition causing infinite loops.
• Not updating variables inside While or Do loops.

Always ensure your loop will end.

vba

Sub WrongLoop()
    Dim i As Integer
    i = 1
    While i <= 5
        Cells(i, 1).Value = i
        ' Missing i = i + 1 causes infinite loop
    Wend
End Sub

Sub CorrectLoop()
    Dim i As Integer
    i = 1
    While i <= 5
        Cells(i, 1).Value = i
        i = i + 1
    Wend
End Sub

Quick Reference

Loop Type	Purpose	Syntax Example
For Loop	Repeat fixed times	For i = 1 To 10 ... Next i
While Loop	Repeat while condition true	While condition ... Wend
Do Loop	Repeat until condition met	Do While condition ... Loop

Key Takeaways

Use For loops to repeat code a set number of times.

Use While or Do loops to repeat based on conditions.

Always update loop variables to avoid infinite loops.

Close For loops with Next and While loops with Wend.

Loops automate repetitive Excel tasks efficiently.