[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.

• For Next Loop
• While Loop
• Do While Loop

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]

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

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.

[/fusion_text]