VBA For Next Loop

For Next Loop in VBA Excel

“For Next” loop is one of those loops which is used very often than other loops in VBA coding. Loops will help us to repeat the same kind of task for specified cells until the condition is TRUE.

FOR LOOP loops through the range of cells and perform a specific task until the condition is TRUE. For example, if you want to loops through the first 10 cells and insert serial numbers incremented by 1, you can use Excel VBA FOR NEXT loop.

For Next loop includes three parameters.

For Statement = Starting Point To End Point

  [Perform Some Task]

Next Statement

From the starting point loop will start and perform some task, after finishing the task its move to the next loop and again perform the same task in the different cell. Like this, until it reaches End Point it keeps performing the specified task.

Step by Step Examples of Using For Next Loop in VBA

Below is the step by step examples of Using VBA For Next Loop in Excel

Assume you want to insert serial numbers from 1 to 10 from A1 to A10. Off course we can insert like writing 10 lines of code like the below one.

Code:

Sub For_Next_Loop_Example1()

  Range("A1").Value = 1
  Range("A2").Value = 2
  Range("A3").Value = 3
  Range("A4").Value = 4
  Range("A5").Value = 5
  Range("A6").Value = 6
  Range("A7").Value = 7
  Range("A8").Value = 8
  Range("A9").Value = 9
  Range("A10").Value = 10

End Sub

The above code also would do the job for me. But what if I want to insert 100 serial numbers, off course I can’t write 100 lines to code just to insert serial numbers. This is where the beauty of “FOR NEXT” loop comes in to picture. Follow the below steps to minimize the code

Step 1: To apply VBA FOR NEXT loop we need to define a variable, I have declared the variable name called “Serial_Number” as an integer data type.

Code:

Sub For_Next_Loop_Example2()

  Dim Serial_Number As Integer

End Sub

Step 2: Now I order to apply VBA FOR NEXT loop in excel lets cercal our object our objective is to insert serial numbers from 1 to 10, so this means our loop has to run for 10 times. So our FOR LOOP statement should be like this.

For Serial_Number = 1 to 10

Next Serial_Number

Code:

Sub For_Next_Loop_Example2()

  Dim Serial_Number As Integer

  For Serial_Number = 1 To 10

  Next Serial_Number

End Sub

Step 3: We have applied the VBA For Next loop, now once the loop is applied we need to specify what we have to do inside the loop. Our objective here is to insert serial numbers from 1 to 10. So write the code as Cells (Serial_Number, 1).Value = Serial_Number.

Code:

Sub For_Next_Loop_Example2()

  Dim Serial_Number As Integer

  Dim Serial_Number = 1 To 10
    Cells(Serial_Number, 1).Value = Serial_Number
  Dim Serial_Number

End Sub

Step 4: The reason why we had given Serial_Number in the VBA CELLS property because we cannot specify the hardcore number for row reference here. Every time loop runs I want to insert the new serial number in the new cell, not in the same cell.

Step 5: This step in the VBA For Next Loop is to run the code line by line by pressing the F8 key.

Step 6: Place a cursor on the variable Serial_Number, it shows the current value of Serial_Number.

At this point in time, the Serial_Number value is zero.

Step 7: Press once more time F8 key, yellow color will move to the next line of code in VBA. Now place a cursor on the Serial_Number.

Now the value of Serial_Number is equal to one, because now loop is started and our loop starting from 1, so variable Serial_Number value is equal to one.

One more interesting thing here is wherever the variable Serial_Number is there that is also equal to 1.

So in the cells property we have mentioned the code as:

Cells(Serial_Number, 1).Value = Serial_Number

This means Cells(1, 1).Value = 1. (Cells (1,1) means Row1 & Column1.

So in row number 1 and column number 1 value should be 1.

Step 8: Now press once more time F8 and see what happens in Row1 & Column1 i.e. cell A1.

So we got 1 as the value i.e. the value of Serial_Number

Step 9: Now press F8 key one more time. Typically next code should be run i.e. the end of the substatement. But here it will go back to the “above line”

Step 10: Remember loop already completed the first run now it returns for the second time. Now place a cursor on the variable Serial_Number and see what the value is.

Now variable Serial_Number is equal to 2 because loop already returned for the second time.

Now wherever Serial_Number is there is equal to the value of 2. So the line code:

Cells(Serial_Number, 1).Value = Serial_Number is equal to:

Cells(2, 1).Value = 2. (Cells (2,1) means Row2 & Column1 i.e. A2 cell).

Step 11: Run this code and see the value in A2 cell.

So, we got the second serial number value.

Step 12: Now press F8 key it will again back to the above line to repeat the code. This time Serial_Number value will be 3.

Step 13: So wherever Serial_Number is there is equal to 3.

So the line code: Cells(Serial_Number, 1).Value = Serial_Number is equal to:

Cells(3, 1).Value = 3. (Cells (3,1) means Row3 & Column1 i.e. A3 cell).

Step 14: Press F8 to execute the highlighted line and see the value in A3 cell.

Step 15: Now keep pressing F8 it will keep inserting the serial numbers. The moment value of the variable Serial_Number is 10 it will stop looping and exit the loop.

Things to Remember Here

• This is just the introduction part to the VBA FOR NEXT loop, in the upcoming articles we will see more advanced examples to VBA FOR NEXT loop.
• VBA For Next Loop needs variable to run the loop. Using this variable we can dynamically reference the cells.

Recommended Articles

This has been a guide to VBA FOR NEXT loop. Here we discuss how to use VBA For Next Loop with step by step examples and downloadable excel template. Below are some useful excel articles related to VBA –

• Excel VBA Sub Procedures
• VBA IsEmpty Function
• Create an Interactive VBA UserForm
• VBA Color Index Property
• VBA OFFSET Function