Today's Goals

Master For...Next loops
Master For Each...Next loops
Master Do While/Until loops

Key Concepts

1. For...Next

A loop repeats a block of code. Use For when you know how many times to repeat:

vba
For i = 1 To 10
    ' runs 10 times
Next i

i is the loop counter and gains 1 each pass. Count down or step wider with Step: For i = 10 To 1 Step -1; leave early with Exit For.

2. For Each...Next

For Each walks a collection (worksheets, cell ranges, and so on) without needing the item count:

vba
For Each item In collection
    ' loop over the collection
Next item

For bulk object processing, For Each reads more naturally than a counted For.

3. Do While / Do Until

Use a Do loop when all you know is when to stop:

vba
Do While condition
    ' runs while the condition holds
Loop

Do Until condition
    ' runs until the condition becomes true
Loop

While keeps going while the condition is true; Until keeps going until it becomes true. A Do loop must advance its own condition variable inside the body (for example i = i + 1), or it never ends — an infinite loop.

Today's Code

vba
Sub For循环示例()
    Dim i As Integer
    Dim sum As Integer

    sum = 0
    For i = 1 To 100
        sum = sum + i
    Next i
    MsgBox "1到100的和: " & sum
End Sub

Sub ForEach示例()
    Dim ws As Worksheet

    For Each ws In Worksheets
        MsgBox ws.Name
    Next ws
End Sub

Sub DoWhile示例()
    Dim i As Integer

    i = 1
    Do While i <= 10
        i = i + 1
    Loop
    MsgBox "循环结束时i的值: " & i
End Sub

Expected results: the For loop pops up 5050; For Each shows each worksheet name in turn; the Do While loop ends with i = 11.

Real-World Scenario

The setup: a sales log holds 30 records in rows 2 through 31, with each order's amount in column C. Month end: total sales, plus a count of orders over 10,000:

vba
Sub 统计销售流水()
    Dim i As Long, 总额 As Double, 大单数 As Long

    For i = 2 To 31
        总额 = 总额 + Cells(i, 3).Value
        If Cells(i, 3).Value >= 10000 Then
            大单数 = 大单数 + 1
        End If
    Next i

    MsgBox "总销售额: " & 总额 & vbCrLf & _
           "过万大单: " & 大单数 & " 笔"
End Sub

The loop counter doubles as the row number: Cells(i, 3) reads column C of row i. If column C totals 118,000 with 4 big orders, the popup shows exactly those two numbers. When the data grows to 300 rows, change 2 To 31 to 2 To 301 — the core case of VBA replacing manual work.

Exercises

Add up the even numbers from 1 to 50
Loop through every worksheet in the workbook
Fill cells A1:A10 with a loop

Exercise Solutions

Run a For loop from 1 to 50 and accumulate when i Mod 2 = 0 (the 练习_计算偶数和 sub in the .bas file is a complete example; the result is 650).
For Each ws In Worksheets visits every sheet (see the ForEach example).
Combine For i = 1 To 10 with Cells:

vba
For i = 1 To 10
    Cells(i, 1).Value = i
Next i

That fills A1:A10 with 1 through 10.

Extension Exercises

Write the 9×9 multiplication table into A1:I9 with nested loops.
Sum the numbers from 1 to 100 that are divisible by 3 but not by 5.
Scan down from row 1 with Do While, find the first empty row, and pop up its row number.

Common Errors and Troubleshooting

Error / Symptom	Cause	Fix
Excel freezes, mouse spinner keeps spinning	Infinite Do loop	Interrupt with `Esc` or `Ctrl+Break`; add the missing increment
`Run-time error '6': Overflow`	An Integer went past 32,767	Make the loop variable a Long
`Run-time error '1004'`	Cells got a 0 or negative row/column number	Rows and columns start at 1; check the boundaries

Summary

Known count → For; walking a collection → For Each; only a stop condition → Do loop. With any Do loop, confirm up front that something inside advances the condition.

由在线工具箱(www.vba.net)整理制作

🌱 Day04 - Loops