Excel Made Easy

VBA Loop Statement FOR...TO...NEXT

If you want to repeat a calculation a number of times, if you want loop through table or your sheet to search for things or do various operations on cell or just repeat things many time, then the FOR TO NEXT loop is what is needed. There are other ways to make loop like WHILE .. END WHILE, FOR .. EACH, DO .. WHILE

Loop in Excel For To Next Eating Glazed Donuts is exactly where you you would use an infinite loop. Because who wants to eat only one donuts, especially the simple glazed one :-)

As VBA is a great programming language, it has also a function to repeat the good things :-). The loop statement is very useful for this,

This is done with the For To Next statement. I allows you to loop through cell, make multiple calculations, scan through a table or multiply donuts.

The next piece of VBA code will multiply donuts for you.

To do this, you start by declaring the variable i as Integer (using i, j, k for the loop variable is kind of a general convention but you can use any other letter or word like "donuts", "counter", etc...)

The you type the the statement FOR i = x TO y STEP z

The loop will start at x, go to y by steps of z

For example FOR i = 4 to 10 step 2. the variable i will take the values 4, 6, 8, 10

The code for this macro looks like this.

Sub Loop_button()

Dim i As Integer

For i = 1 To 10

Cells(7, i).value = i & " donuts"
' write the number i + the word donuts behind

Next i

End Sub

Here you can see the effect of the FOR TO Loop. It multiplied the donuts.

Double FOR..TO...NEXT loop

you can do more complex things like have a double loop in VBA. This is useful for example to browse through a 2 dimensional table or to create even more donuts.

You can also make it a triple or quadruple loop.

Tips: Using the standard letter counters like i, j, k might then be confusing so use something more specific like loop1, loop2, loop3 or even more specific name. And comment your code.

Look at this example.

Sub double_loop()

Dim hor, vert, counter As Integer

counter = 0
' set the donuts counter to 0

For vert = 1 To 10 Step 2

    For hor = 1 To 10 Step 2

        counter = counter + 1

        Cells(vert + 15, hor).value = counter & " donuts"
' it will start the display in row 15 (+1)

    Next hor

Next vert

End Sub

And this is how it look. It creates a 2 dimensional table of Donuts.

loop in excel for to next

This is the basic concept for the FOR TO STEP NEXT Statement.

We hope this could help you and you will use it a lot.

This should give you enough to start programming message boxes in VBA by yourself. Good Luck.

Please Tweet, Like or Share us if you enjoyed.