WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA For Next Loop

VBA For Next Loop

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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

VBA For Next Loop

How to use VBA For Next Loop?

You can download this VBA For Next Loop Excel Template here – VBA For Next Loop Excel Template

Assume you want to insert serial numbers from 1 to 10 to A1 to A10 cells. 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 I want to insert 100 serial numbers? Of course, I can’t write 100 lines of code just to insert serial numbers. This is where the beauty of the “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 ten 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

VBA for Next Loop Step 1

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

VBA for Next Loop Step 3

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.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VBA for Next Step 4

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

VBA for Next Loop Step 6

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.

VBA for Next Loop Step 7

Now the value of Serial_Number is equal to one because now loop is started and our loop starting from 1, so the 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, the value should be 1.

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

VBA for Next Loop Step 8

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., the end of the substatement. But here, it will go back to the “above line.”

Step 9

Step 10: Remember the 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.

Step 10

Now variable Serial_Number is equal to 2 because the 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 the A2 cell.

Step 11

So, we got the second serial number value.

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

VBA for Next Step 12

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 13

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.

Step 14

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 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
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA For Next Loop Excel Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More