myITSchools

For loop in excel VBA

A For loop in excel VBA is a programming construct that allows you to execute a block of code repeatedly for a specified number of times. In VBA, you can use a For loop to loop through a range of cells with just a few lines of code.

The syntax of a For loop in VBA is

For counter = start to end

    ‘Write your code here

Next counter

Here, counter is a variable that is used to count the number of times the loop has executed. start and end are the initial and final values of the counter, respectively. The loop will execute the code between For and Next statements for each value of counter from start to end.

Here’s an example of a For loop in VBA, suppose we want to print 1 to 10 number from cell A1 then the code will be as follow. It is also called as “single loop” i.e. loop through a ‘one-dimensional range of cells’.

Sub for_loop ()
' Here variable x is consider as row no.
Dim x As Integer
For x = 1 To 10

Range (“A” & x).value=x

Next x
End Sub

Nested for Loop in Excel VBA

We can use a double loop to loop through a two-dimensional range of cells. For example, suppose we want to print 1 to 10 from Cell A1 to A10 at the same time we want to print 1 to 5 in Cell B1 to B5 then VBA code the this will be as follow.

Sub Nested_loop ()
Dim x As Integer
Dim y As Integer
For x = 1 To 10
For y = 1 To 5

Range ("A" & x). Value = x
Range ("B" & y). Value = y

Next y
Next x
End Sub

Same way we can put one for loop inside loop. You can use a triple loop to loop through two-dimensional ranges on multiple Excel worksheets. For example,

Sub test1233()
Dim x As Integer
Dim y As Integer
Dim z As Integer
For x = 1 To 10
For y = 1 To 10
For z = 1 To 10

Range ("A" & x). Value = x * 12
Range ("B" & y). Value = y * 13
Range ("C" & z). Value = z * 14
Next z
Next y
Next x
End Sub

How to use [Step Size] in for loop?

When we use for loop in excel VBA, generally the increment each time in for loop is 1. The Step functionality enables us to control the increment as well as decrement means we can put positive as well as negative value.

The syntax of a For loop in VBA with Step is as follow

For counter = start to end [Step size]

    ‘Write your code here

Next counter

Explanation: –

  • Counter: It’s variable that stores the count of the repetition.
  • Start: A numeric variable that stores the starting value for the counter.
  • End: A numeric variable that stores the ending value for the counter.
  • Step Size: This variable specifies increase or decrease in the value of counter. By default, the value of step size is one, but you can also use any positive or negative value.
  • Statement: It’s a set of code do you want to execute in each iteration of the For Next loop.
  • Next: It’s the end statement for one iteration of loop and uses the counter value to tell VBA to repeat the.

For example, run below code with step count as 2

Sub for_loop ()
' Here variable x is consider as row no.
Dim x As Integer
For x = 1 To 10 Step 2

Range (“A” & x).value=x

Next x
End Sub

The above VBA code with result as 1,3,5,7,9 from cell A1 to A10

Scroll to Top