For Loop Syntax
The For Next Loop allows you to repeat a block of code a specified number of times. The syntax is:
|
[Dim Counter as
Integer]
For Counter = Start to
End [Step Value]
[Do
Something]
Next [Counter]
|
Count to 10
This code will count to 10 using a For-Next Loop:
|
Sub
ForEach_CountTo10()
Dim n As Integer
For n = 1 To 10
MsgBox n
Next n
End Sub
|
Count to 10 – Only Even Numbers
This code will count to 10 only counting even numbers:
|
Sub For_CountTo10_Even()
Dim n As Integer
For n = 2 To 10 Step 2
MsgBox n
Next n
End Sub
|
Countdown from 10 to 1
This code will countdown from 10 to 1 by using reverse loop
|
Sub For_Countdown_Reverse()
Dim n As Integer
For n = 10 To 1 Step -1
MsgBox n
Next n
End Sub
|
Delete Rows if Cell is Blank
Note : If you loop from the top rows to the bottom rows, as you delete rows you will mess up your counter.
This example will delete rows with blank cells (starting from the bottom row):
|
Sub For_DeleteRows_BlankCells()
Dim n As Integer
For n = 10 To 1 Step -1
If Range("a"
& n).Value = "" Then
Range("a" & n).EntireRow.Delete
End If
Next n
End
|
Nested For Loop
You can “nest” one For Loop inside another For Loop. We will use Nested For Loops to create a multiplication table:
|
Sub Nested_For_MultiplicationTable()
Dim row As Integer, col As Integer
For row = 1 To 9
For col = 1 To 9
Cells(row + 1, col
+ 1).Value = row * col
Next col
Next row
End Sub
|
Exit For
The Exit For statement allows you to exit a For Next loop immediately.
You would usually use Exit For along with an If Statement, exiting the For Next Loop if a certain condition is met.
|
Sub ExitFor_Loop()
Dim i As Integer
For i = 1 To 1000
If Range("A"
& i).Value = "error" Then
Range("A" & i).Select
MsgBox "Error
Found"
Exit For
End If
Next i
End Sub
|
0 Comments