VBA For Next Loop

Excel VBA For Next Loop

VBA For Next loop is a loop which is 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 is reached and when the criteria is reached the next statement directs the procedure to the next step of the code.

“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 loop through the first 10 cells and insert serial numbers incremented by 1, you can use FOR NEXT loop.

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

  [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 endpoint it keeps performing the specified task.

 

How to use VBA For Next Loop?

Assume you want to insert serial numbers from 1 to 10 to A1 to A10 cell. Off course we can insert like writing 10 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 I want to insert 100 serial numbers, off course I can’t write 100 lines of 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: First 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 FOR NEXT loop, our objective is to insert serial numbers from 1 to 10, so this means our loop has to run for 10 times. So 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, 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 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: 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, the 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 F8 one more time 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.

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 –

• VBA Close UserForm | Using Unload Me Statement
• Convert String in Excel VBA
• Excel VBA Data Type
• Excel VBA Sub Procedures
• VBA IsEmpty Function
• Create an Interactive VBA UserForm
• VBA Color Index Property
• VBA OFFSET Function