Loop is very useful feature of VBA. It is used wherein we need to run a certain code block again and again. There are 5 types of loops in VBA.
- For Loop
- For… Each Loop
- While … wend Loop
- Do … while Loop
- Do… until Loop
In this chapter you will learn about For Loop, Steps in For Loop, Exit from for loop, For… Each Loop, While … wend Loop, Do … while Loop and Do… until Loop.
For Loop
For Loop is used commonly in the VBA. It’s allowed to run the certain code block as per the given number.
For example, we want to write the table of 12 on column A.
Sub For_Loop()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("ForLoop")
Dim i As Integer
For i = 1 To 10
sh.Range("A" & i).Value = 12 * i
Next i
End Sub
After executing the above code table to 12 will be on column A
Step in For Loop
We can use Step in for loop. By default for loop take 1 step. We can change it by using Step keyword.
Let’s say we want to type “PK-AnExcelExpert.com” on Column C in alternate rows
Sub For_Loop_Step()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("ForLoop")
Dim i As Integer
For i = 1 To 10 Step 2
sh.Range("C" & i).Value = "PK-AnExcelExpert.com"
Next i
sh.Columns("C:C").AutoFit
End Sub
“PK-AnExcelExpert.com” will typed on alternate row till row number 10
Exit from for loop
We can exit form the For loop by using Exit for statement.
For example, we have the list of country wise sales. now we want to message of India’s sale then we will run the loop one by one in county list as soon as we get the India in the list we can exit the loop because once we have got our desired result no need to run the loop on entire list.
Sub Exit_For_Statement()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("ForLoop")
Dim i As Integer
For i = 2 To sh.Range("F" & Application.Rows.Count).End(xlUp).Row
If sh.Range("F" & i).Value = "India" Then
MsgBox "Sales of India is " & sh.Range("G" & i).Value
Exit For
End If
Next i
End Sub
In this example loop will run till India only.
For… Each Loop
For each loop works with the collection or group.
Let’s say want to message the worksheets names of this workbook
Sub For_Each()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
MsgBox sh.Name
Next
End Sub
While…Wend loop
In a While…Wend loop, if the condition is True, all the statements are executed until the Wend keyword is encountered.
For example we want to type table of 20 on column A. In the below given example code will execute until the value of i is less than 11.
Sub While_Wend_Loop()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("While Wend Loop")
Dim i As Integer
i = 1
While i < 11
sh.Range("A" & i).Value = 20 * i
i = i + 1 'increment of 1 in i
Wend
End Sub
Table of 20 will available on column A after executing this code.
Do … while Loop
Do…While loop is used when we want to repeat a set of statements as long as the condition is true. Loop will be completed as soon as the condition is false.
For example, we want to write the table 10 in column A.
Sub Do_While_Loop()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Do While Loop")
Dim i As Integer
i = 1
Do While i < 11
sh.Range("A" & i).Value = 10 * i
i = i + 1 'increment of 1 in i
Loop
End Sub
Table of 10 will available on column A after executing this code.
Do … until Loop
A Do…Until loop is used when we want to repeat a set of statements as long as the condition is false. Loop will be completed as soon as the condition is true.
For example, we want to write the table 15 in column A.
Sub Do_Until_Loop()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Do Until Loop")
Dim i As Integer
i = 1
Do Until i = 11
sh.Range("A" & i).Value = 15 * i
i = i + 1 'increment of 1 in i
Loop
End Sub
Table of 15 will available on column A after executing this code.
