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

Sometimes you want loop through table or your sheet to search for things or do various operations on cell or just repeat things, like eating a donuts and 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.

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.