iLearn Excel VBA: For-Loop

For-Loops Spin You Right Round

The For-Loop is an iteration statement. An iteration statement is one that repeats a segment of code until a desired result is achieved. Each repetition is called an iteration. Again, it’s easier to learn by example:

Sub TheForLoop()
    For i = 1 To 10
        Debug.Print i
    Next i
End Sub

After running the procedure above you will see 1 through 10 printed in your immediate window. In line 2 we declare the For-Loop and create a variable i which is equal to 1. In the same line we ask for i to increase by 1 until it equals 10 which will end the loop. Line 4 tells us to increase i by 1 and the code goes back to line 2. Here we can see the looping effect first hand.

Wait a For-Loop within a For-Loop?

This lesson is fairly short so let’s delve into a more advanced looping technique which you will likely use in the future . . . The Double For-Loop. It’s exactly what it sounds like, its a For-Loop with a For-Loop inside of it. Let’s say we wanted to write values in a 8×4 grid in your excel sheet a Double For-Loop could be used to access these cells and write the values. Take a look at this code, execute it and see if you can tell what is happening:

Sub TheForLoop()
    For i = 1 To 8
        For j = 1 To 4
            Cells(i, j) = 100
        Next j
    Next i
End Sub

Line 4 is new. This is how you write values into cells. This will be discussed later, but essentially, Cells(1,1) is row 1 column A, Cells(1, 2) is row 1 column B and so on. The best way to understand code is to follow it line by line. See if you can tell the order in which the lines are executed (ANSWER: 1, 2, 3-4-5, 3-4-5, 3-4-5, 3-4-5, 6, 2, 3-4-5, 3-4-5, 3-4-5, 3-4-5, 6, see the pattern?). Note that a For-Loop doesn’t always need to start at 1, it can start at any number. Next time we’ll talk about the Do-While-Loop, signing off ~iAtoria.