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.
300-101 400-101 300-320 300-070 300-206 200-310 300-135 300-208 810-403 400-050 640-916 642-997 300-209 400-201 200-355 352-001 642-999 350-080 MB2-712 400-051 C2150-606 1Z0-434 1Z0-146 C2090-919 C9560-655 642-64 100-101 CQE CSSLP 200-125 210-060 210-065 210-260 220-801 220-802 220-901 220-902 2V0-620 2V0-621 2V0-621D 300-075 300-115 AWS-SYSOPS 640-692 640-911 1Z0-144 1z0-434 1Z0-803 1Z0-804 000-089 000-105 70-246 70-270 70-346 70-347 70-410