Excel Macro Tutorial : For Next Loop in Excel Macro

January 12, 2012| Posted by : | Filled in Excel Macro Tutorial

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.


Use of VBA For Next loop

In this Article you will learn about For Next loop. This is the most frequently used loop. It enables you to execute a set statements multiple number of iterations. It requires an Index or Incremental variable which counts the total number of iterations. Syntax for this loop is shown below:
Syntax



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.

How to Exit from For Loop, before completing the complete iterations:

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 that at a point, if some other condition inside the loop, is failed then No matter what value is there in variable “iCount”, loop should terminate immediately. For that you just need to use Exit For to terminate the loop immediately.


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.

So far in the above examples i was incrementing by 1 by default. If you want to increase the Value of i by a specific value each time then follow the below Syntax:

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
How to decrease the value of “iCount” in each iteration

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. Let’s take the same above example. In the above example, you are adding the value of “iCount” from 1 to 10. It will be same as if we are adding from 10 to 1. In such case, we just need to put the interval as a “Negative” value by which you want to decrease the value of “i”.

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

Comments

comments

Related posts:

  1. Excel Macro Tutorial : VBA Spin Button In the previous Article How to write Excel Macro –...

Subscribe To Get New Posts delivered directly to your Mailbox

2 Responses to “Excel Macro Tutorial : For Next Loop in Excel Macro”

  1. [...] previous Article, we had seen about For…Next Loop.. In this article we are going to see about While and Do While [...]

  2. [...] in Excel. You also have learnt some basic Programming Lessons as well like If Else Statements, For Loop, While and DO While Loops [...]

Leave a Reply

  • About
  • Downloads
  • Excel Formula
  • Excel Tips
  • Excel Macro Tutorial
  • VBA Functions
  • Archives
  • Contact
  • Excel Formula
  • Excel Functions
  • Excel Macro
  • Excel Macro Basics
  • Excel Macro Tutorial
  • Excel Tips
  • HP QC
  • Interesting VBA Functions
  • New to Excel Macro ?
  • Personal
  • Popular Articles
  • 2014
  • 2013
  • 2012
  • 2011