Saturday, December 1, 2012

How to use For Next Loop, Do While Loop and Do Until Loop in VBA

VBA Loop Structures-For Next Loop,Do While Loop and Do Until Loop 
For ... Next Loop :
Use For ... Next loop if the number of loops is already defined and known. A For ... Next loop uses a counter variable that increases or decreases in value during each iteration of the loop.
Example:
For i = 1 to 10
Cells(i, 1) = i
Next I
In this example, i is the counter variable from 1 to 10. The looping process will send value to the first column of the active sheet and print i (which is 1 to 10) to row 1 to 10 of that column.
Note that the counter variable, by default, increases by an increment of 1.

For ... Next Loop With Step :
You can use the Step Keyword to specify a different increment for the counter variable.
Example:
You can also have decrement in the loop by assign a negative value after the Step keyword.
For i = 10 to 1 Step -2
Cells(i, 1) = i
Next I

This looping process will print values with an increment of -2 starts from 10 on row 10, 8, 6, 4 and 2 on column one.

For i = 1 to 10 Step 2
Cells(i, 1) = i
Next i

This looping process will print values with an increment of 2 on row 1, 3, 5, 7 and 9 on..
Do While ... Loop :
You can use the Do While ... Loop to test a condition at the start of the loop. It will run the loop as long as the condition is ture and stops when the condition becomes false. For Example:
i = 1
Do While i =< 10
Cells(i, 1) = i
i = i + 1
Loop

This looping process yields the same result as in the For ... Next structure example.


One thing to be caution is that sometimes the loop might be a infinite loop. And it happens when the condition never becomes false. In such case, you can stop the loop by press [ESC] or [CTRL] +[BREAK].

Do Until ... Loop :
You can test the condition at the beginning of the loop and then run the loop until the test condition becomes true.
Example:
i = 1
Do Until i = 11
Cells(i, 1) = i
i = i + 1
Loop

This looping process yields the same result as in the For ... Next structure example.


Do ... Loop While :
When you want to make sure that the loop will run at least once, you can put the test at the end of loop. The loop will stop when the condition becomes false. (compare this loop structure to the Do ... While Loop.)
Example:

i = 1
Do
Cells(i, 1) = i
i = i + 1
Loop While i < 11

This looping process yields the same result as in the For ... Next structure example.


Do ... Loop Until
This loop structure, like the Do ... Loop While, makes sure that the loop will run at least once, you can put the test at the end of loop. The loop will stop when the condition becomes true. (compare this loop structure to the Do ... Until Loop.)
Example:
i = 1
Do
Cells(i, 1) = i
i = i + 1
Loop Until i = 11

This looping process yields the same result as in the For ... Next structure example.


Thanks,Tamatam

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog