Saturday, 1 December 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. Example:

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 structures 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 structures 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 structures 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 structures example.


Thanks
Tamatam


1 comment:

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts