VBA Loops
Written by: maffelu , 2009-07-29 19:59:28
How to loop in VBA
There are four standard ways to loop in VBA and they work like in most other programming languages, they are as follows:
- Do-Until
- Do-While
- For-Next
- For Each
We'll look through each of them and at the end of this article there will be an example sub to test with and a screen shot of what it outputs.
There will be two variables declared already:
Dim row As Integer
Dim column As Integer
The loops
Do-Until
'*****************
'The Do-Until loop
'-----------------
'
'Syntax:
'Do
' [Do things here]
'Loop Until [expression]
'*****************
row = 1
column = 1
Cells(row, column).Select
Do
Cells(row, column).Value = "Do-Until " + CStr(row)
row = row + 1
Loop Until row = 5
This loop will loop until an expression is true. It works similar to the Do-While but will run at least one time.
In our example we increment the variable row (which start with the value 1) every loop until it reaches the value 5.
Do-While
'*****************
'The Do-While loop
'-----------------
'
'Syntax:
'Do While [expression]
' [Do things here]
'Loop
'*****************
row = 1
column = 2
Cells(row, column).Select
Do While (row < 5)
Cells(row, column).Value = "Do-While " + CStr(row)
row = row + 1
Loop
This is similar to the Do-Until loop but won't always run, e.g. it can fail directly if the expression is true.
For-Next
'*****************
'The For-Next loop
'-----------------
'
'Syntax:
'For [var = startValue] to [stopValue]
' [Do things here]
'Next [var]
'*****************
column = 1
Cells(row, column).Select
For row = 6 To 9
Cells(row, column).Value = "For-Next " + CStr(row)
Next row
The For-Next loop need a start and a stop value. It will then loop until the start value equals the stop value. Needless to say, the start value really should be set lower than the stop value.
For Each
'*****************
'The For-Each loop
'-----------------
'
'Syntax:
'For Each [var] in [collection]
' [Do things]
'Next [var]
'*****************
row = 6
column = 2
Cells(row, column).Select
Dim collection(0 To 3) As String
collection(0) = "For Each 1"
collection(1) = "For Each 2"
collection(2) = "For Each 3"
collection(3) = "For Each 4"
For Each thing In collection
Cells(row, column).Value = thing
row = row + 1
Next thing
The For Each loop loops through a collection of objects, in this case, an array.
The complete sub
Public Sub looping()
Dim row As Integer
Dim column As Integer
'*****************
'The Do-Until loop
'-----------------
'
'Syntax:
'Do
' [Do things here]
'Loop Until [expression]
'*****************
row = 1
column = 1
Cells(row, column).Select
Do
Cells(row, column).Value = "Do-Until " + CStr(row)
row = row + 1
Loop Until row = 5
'*****************
'The Do-While loop
'-----------------
'
'Syntax:
'Do While [expression]
' [Do things here]
'Loop
'*****************
row = 1
column = 2
Cells(row, column).Select
Do While (row < 5)
Cells(row, column).Value = "Do-While " + CStr(row)
row = row + 1
Loop
'*****************
'The For-Next loop
'-----------------
'
'Syntax:
'For [var = startValue] to [stopValue]
' [Do things here]
'Next [var]
'*****************
column = 1
Cells(row, column).Select
For row = 6 To 9
Cells(row, column).Value = "For-Next " + CStr(row)
Next row
'*****************
'The For-Each loop
'-----------------
'
'Syntax:
'For Each [var] in [collection]
' [Do things]
'Next [var]
'*****************
row = 6
column = 2
Cells(row, column).Select
Dim collection(0 To 3) As String
collection(0) = "For Each 1"
collection(1) = "For Each 2"
collection(2) = "For Each 3"
collection(3) = "For Each 4"
For Each thing In collection
Cells(row, column).Value = thing
row = row + 1
Next thing
End Sub
If you run this, this will be the outcome:
There are 4 comments on this article.
maffelu
2010-05-22 01:44:22
Well, could you be a bit more specific. Do you want to create an autocomplete function?
What element are you working with, a cell or a textbox?
Waldemar D. Cap
2010-10-04 06:57:42
elow!..
i need help for my report.my code isnt working.i need only code for cut and paste.heres the problem. i have 3 column. 1st column header is adopted, 2nd column header is unadopted then the 3rd column header is remarks.i have details in unadopted column. if theres a remarks ok. then the details in unadopted cut and paste in the adopted column..hhmm..hope you can help me..
maffelu
2010-10-04 13:06:38
Well, I don't hand out code, sorry. However I would suggest that you record a macro where you do exactly what you are talking about and take a look at that code.
Recording macros is one of the best ways to figure out how to do things in VBA.
Leave a message!
If you have any question or just want to leave a message, just fill out the form below!
Your e-mail will not be visible in your post, it is for validation reasons only
Maffelu
maurizio
2010-05-21 03:09:39
Hi,
I want to write a macro using a For each statement to emilate the Autofill Command for series of numbers.
Any ideas?