Excel Macro Tutorial : For Next Loop in Excel Macro

.

[fullwidth background_color=”” background_image=”” background_parallax=”none” enable_mobile=”no” parallax_speed=”0.3″ background_repeat=”no-repeat” background_position=”left top” video_url=”” video_aspect_ratio=”16:9″ video_webm=”” video_mp4=”” video_ogv=”” video_preview_image=”” overlay_color=”” overlay_opacity=”0.5″ video_mute=”yes” video_loop=”yes” fade=”no” border_size=”0px” border_color=”” border_style=”” padding_top=”20″ padding_bottom=”20″ padding_left=”” padding_right=”” hundred_percent=”no” equal_height_columns=”no” hide_on_mobile=”no” menu_anchor=”” class=”” id=””][one_full last=”yes” spacing=”yes” center_content=”no” hide_on_mobile=”no” background_color=”” background_image=”” background_repeat=”no-repeat” background_position=”left top” hover_type=”none” link=”” border_position=”all” border_size=”0px” border_color=”” border_style=”” padding=”” margin_top=”” margin_bottom=”” animation_type=”” animation_direction=”” animation_speed=”0.1″ animation_offset=”” class=”” id=””][title size=”1″ content_align=”left” style_type=”underline solid” sep_color=”” margin_top=”” margin_bottom=”” class=”” id=””]Beginners guide for how to use FOR Loop in Excel VBA[/title][fusion_text]Keeping the beginners in mind, I am going to write this article about For Next loop in Excel Macro. Also one of my friend wanted such post to be published. First of all i would like to thank for such input and feedback. Even if you want any help or any article to be published, kindly mail me.

If you want to learn Excel Macros then read articles from Excel Macro Tutorial

So coming to the Article, mainly there are following Types of Loops which are used.

In this Article, I am going to Explain you about For Next Loop. For Other Two loops While and Do While Loop, you can refer the next article.


[/fusion_text][/one_full][title size=”1″ content_align=”left” style_type=”underline solid” sep_color=”” margin_top=”” margin_bottom=”” class=”” id=””]How to use FOR Loop in Excel VBA[/title][fusion_text]In this Article you will learn about For Next loop. This is the most frequently used loop. It enables you to execute a set of VBA statements multiple number of iterations.

Since it is a loop, it requires

1. An Index or Incremental variable which counts the total number of iterations after completion of each iteration..

2. Such a condition where this loop should end.

Note: Be careful with this condition. If it is such a condition which may never end, then it is infinite loop and your program will crash.
[/fusion_text][title size=”2″ content_align=”left” style_type=”underline solid” sep_color=”” margin_top=”” margin_bottom=”” class=”” id=””]FOR LOOP Syntax in Excel VBA[/title][fusion_text]

For index=start to end
[statements]
………………….
………………….
………………….
Next

Example:Now let’s take an example to understand how exactly this for loop works. Suppose you need to calculate the Sum of first 10 Numbers from 1 to 10.

Sum = 0
For iCount = 1 To 10
Sum = Sum + iCount
Next
MsgBox Sum

Explanation: In the above example Variable “i” is the index variable which is set to 1 from Start. So in the first iteration the Value of i is 1. Control will go inside the loop and execute the statement like “Sum=Sum + iCount”. When it reaches to “Next” then by default it will increase the value if i by 1. So for the next iteration the value of “iCount” will be 1+1=2.
Every time before getting in to the loop, one condition (i<=10) will be checked. This condition is not specifically mentioned in the above code, but this is the For Loop property. Loop will keep going until the value of “i” reaches to 10.
When the value of i reached to 11 then the Condition (iCount<=10) is failed and then control will come out of the For Loop and loop will be ended.[/fusion_text][/fullwidth][title size=”1″ content_align=”left” style_type=”underline” sep_color=”” margin_top=”” margin_bottom=”” class=”” id=””]How to Exit from For Loop in Excel VBA[/title][fusion_text]Now as you can see, in the above example, For..Loop will run for 10 times and then only it will exit from the loop. Sometimes when you want to exit from the loop even before the FOR condition is failed. Using Exit For one can terminate the for loop any given point of time.

Sum = 0
For iCount = 1 To 10
Sum = Sum + iCount
If Sum > 30 Then Exit For
Next
MsgBox Sum

In the above example as soon as the Value of Sum is greater than 30 then Loop will be terminated though the value of “iCount” is still less than 10.

[/fusion_text][title size=”1″ content_align=”left” style_type=”underline” sep_color=”” margin_top=”” margin_bottom=”” class=”” id=””]What is For Loop with Step[/title][fusion_text]So far you see that every time For Loop index is incremented by 1. So what if you want to increase the index by 2, 3, etc. each time?

It’s simple, here is the syntax to use STEP keyword in FOR NEXT Loop in excel VBA.

Syntax

For [index=start] to [end] Step [interval]
[statements]
………………….
………………….
………………….
Next

Example:In the below example in each iteration the Value of i will be incremented by 2, instead of 1. If we don’t mention Step [interval] then by default it increases by 1.

Sum = 0
For iCount = 1 To 10 Step 2
Sum = Sum + iCount
Next
MsgBox Sum

[/fusion_text][title size=”1″ content_align=”left” style_type=”underline” sep_color=”” margin_top=”” margin_bottom=”” class=”” id=””]For Loop in Descending Order[/title][fusion_text]Sometimes you need to Run your For..Next Loop from a bigger value to a smaller value. It means in every iteration, you want to decrease the Value of “iCount” by a specific interval.

Now as you know how to set interval for each iteration in your For Next Loop using STEP keyword, all you need to set to interval to a negative value. This way after every iteration your For Next Loop iteration value will be decreased by specified interval.

Example:Now let’s take an example to understand how exactly this for loop works. Suppose you need to calculate the Sum of first 10 Numbers from 1 to 10.

Sum = 0
For iCount = 10 To 1 Step -1
Sum = Sum + iCount
Next
MsgBox Sum

In the above example, “iCount” value will start from 10 and it will be decreased by 1 every time because the interval value is set to -1.

In the Next Article, you learn about While and Do-While Loop.

 

To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial

[/fusion_text]

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

0 Comments

Trackbacks/Pingbacks

  1. Learn Excel Macro While and Do While Loop in Excel VBA - [...] previous Article, we had seen about For…Next Loop.. In this article we are going to see about While and…
  2. Learn Excel Macro Excel Macro Tutorial : Basics of VBA (Basic Syntaxes required while coding) - [...] in Excel. You also have learnt some basic Programming Lessons as well like If Else Statements, For Loop, While…
  3. Learn Excel Macro How to refer Values from a Named Range - Excel VBA - [...] you know that you can read values from an array using for loop. So the above values from the…
  4. Excel Macro Tutorial : While Loop and Do While Loop in Excel VBA - Welcome to LearnExcelMacro.com - […] previous Article, we had seen about For Next Loop. In this article we are going to see about While…

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest