How to use Microsoft VBA Loops:
Loops are used in VBA (Visual Basic for Applications) to repeat the same set of programming instructions several times to iterate through variables incrementally (For Loops) through items in a collection, (For Each Loops) such as through all of the worksheets in a workbook or (Do Loops) through a subroutine until a condition is met.
The following are some of the common types of For Loops (if you need to terminate prematurely based on a certain condition use Exit For):
Increment a variable by 1 each loop pass (starting at 1)
——————–
Dim i As Long
For i = 1 To 10
Next i
——————–
Note: This loop will increment the variable i by 1 each pass starting from 1 and ending at 10. If you want to modify the cells in column A from 1 to 10, you could refer to the range within the VBA code as Range(“A” & i) and each pass you will be referencing a different row.
Increment a variable by 1 each loop pass (not starting at 1)
——————–
Dim i as Long
For i = -5 To 10
Next i
——————–
or
——————–
Dim i as Long
For i = 10 To 20
Next i
——————–
Increment a variable by x each loop pass (x = 2)
——————–
Dim i as Long
For i = 1 To 20 Step 2
Next i
——————–
Increment a variable by -x each loop pass (x = -1)
——————–
Dim i as Long
For i = 10 To 1 Step -1
Next i
——————–
Note: This example will run in reverse and increment by -1 each pass.
The following is a common type of For Each Loop (if you need to terminate prematurely based on a certain condition use Exit For):
——————–
Dim WS As Worksheet
For Each WS In Worksheets
Next
——————–
Note: In each pass through the loop you can use the WS variable within the VBA code to reference the specific worksheet in the worksheets collection, and each pass you will be referencing a different worksheet.
The following are some of the common types of Do Loops (if you need to terminate prematurely based on a certain condition use Exit Do):
Run until condition is met
——————–
Do Until
Loop
——————–
Run while condition is met
——————–
Do While
q Loop
——————–
Specify condition check on loop line
——————–
Do
Loop While
——————–
Note: The difference in these methods is that the Do Until or Do While loops may skip all iterations if the initial condition is False; however, the Loop While example will always run at least one iteration.