VBA For Next Loop

VBA For Next Loop is a loop used amongst all the programming languages. In this loop, there is a criterion after the For statement, for which the code loops in the loop until the criteria reach. When the criteria reach, the next statement directs the procedure to the next step of the code.

VBA For Next Loop is one of those loops used more often than other loops in VBA coding because loops will help us repeat the same task for specified cells until the condition is TRUE.

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

This loop needs a variable to run the loop. Using this variable, we can dynamically reference the cells.

It includes three parameters.

For Statement = Starting Point To End Point

  

Next Statement

From the starting point loop will start and perform some tasks. After finishing the task, it moves to the next loop and performs the same task in a different cell. Like this, until it reaches the endpoint, it performs the specified task.

Assume you want to insert serial numbers from 1 to 10 to A1 to A10 cells. Then, of course, we can insert like writing ten lines of code.

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

But what if we want to insert 100 serial numbers? Of course, we cannot write 100 lines of code to insert serial numbers. It is where the beauty of the “FOR NEXT” loop comes into the picture. Follow the below steps to minimize the code.

Step 1: First, we need to define a variable. We have declared the variable name “Serial_Number” as an Integer data type.

Code:

Sub For_Next_Loop_Example2()

  Dim Serial_Number As Integer

End Sub

Step 2: Now, we order to apply For Next Loop. Our objective is to insert serial numbers from 1 to 10, so our loop has to run ten times. So, the 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 loop. 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: We had given Serial_Number in the CELLS property because we cannot specify the hardcore number for row reference here. Every time loop runs, we want to insert the new serial number in the new cell, not in the same cell.

Step 5: Run the code 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, the Serial_Number value is zero.

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

The value of Serial_Number is equal to one because the loop is starting. Our loop starts from 1, so the variable Serial_Number value equals one.

Another interesting thing is that 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

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

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

Step 8: Now, press F8 and see what happens in Row1 and Column1, i.e., cell A1.

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

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

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

The variable Serial_Number equals 2 because the loop has returned.

Now, wherever Serial_Number is, it equals 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 the A2 cell.

So, we got the second serial number value.

Step 12: Now press the F8 key. It will again go back to the above line to repeat the code. This time the 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 the A3 cell.

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

This article has been a guide to VBA FOR NEXT Loop. Here, we discuss using VBA For Next Loop with step-by-step examples and a downloadable Excel template. Below are some useful Excel articles related to VBA: -

• VBA Data Type
• VBA IsEmpty
• VBA Do Loop
• VBA Do Until Loop