Loops in Excel VBA

Loops are great tool in Excel VBA to repeat your actions. If I tell you a simple example about loop then it would be better for you to understand. I believe that you have already worked with some WorksheetFunction like SUMIFS, INDEX-MATCH, COUNTIFS, SUM etc. And few times you did not locked the Lookup_Value or Criteria due to you wish to use the same function for multiple times but different lookup_values and criteria.

In Excel VBA Loops are great tool to repeat your actions how many times you have set. While you always need the same command on different cells with a specific cell difference, then you can use Loop. It will reduce your work and time and give the result faster.



Types of Loops

There are different types of Loops in Excel VBA. Based on your task you have to decide which Loop will match better for your task. In Excel VBA There are 5 different types of Loops are available. These are:

1. For ... Next
2. For ... Each ... Next
3. Do ... While
4. Do ... Until
5. While ... Wend

All of these Loops are explained below:



For ... Next

This loop is very popular to Excel VBA users. It is quite easy to understand. Also it is simple and effective way to repeat your actions for a specified number of times you mention. Personally I like this loop very much. For example if you wish to add 10 worksheets in your current workbook, then you can use below code:

Sub wsadding()
    Dim ws As Integer
    For ws = 1 To 10
        Worksheets.Add
    Next ws
End Sub

Another example is, assume that you wish to find the result by adding 1 + 2 + 3 + 4 + 5 + 6   + 7. It is quite easy but assume that you wish to find the same thing on 1 to 200 then it is difficult to type it manually and calculate them. The For ... Next loop can help you to find the result in an easy way. Use the below code and you will get the result 20100.

Sub sum200()
    Dim x As Long
    Dim i As Integer
    For i = 1 To 200
      x = x + i
    Next i
    Cells(1, 1).Value = x
End Sub

For ... Each ... Next

This loop is similar to For Next loop but the difference is, For Next loop counts mentioned number like: X = 1 to 200, where X is a number which increased by Step 0.1, 1, 3, 4, -1 or something. But in the For Each Next loop X is a set of objects like every sheets of a workbook. Every Cells of a range etc. For example, if you wish to show all the sheet name, through message in your current workbook. Then use the below code and run it:

Sub wsNameDisplay()
   Dim ws As Worksheet
   For Each ws In ThisWorkbook.Sheets
      MsgBox (ws.Name)
   Next
End Sub

Image 1: For Each Next loop example

If you Click the Ok button then the loop will return again the next page name until it's end.


Another Example I want to show here because it is an important loop for Array Formula in Excel VBA. Assume that, you have a range A2:A10 which contains Product_Code like below:

Image 2: Product_Code

Now you wish to remove the "-". You can do it with For Next loop. But here I'm going to use For Each Next loop. Use the below code:

Sub ForEachLoop1()
Dim x As Object
    For Each x In Range("A2:A10")
        x.Value = Replace(x, "-", "")
    Next
End Sub

The result is:

Image 3: Product Code with "-" removed

Do ... While ... Loop:

This is another type of Loop in Excel VBA. It continues the Loop while a condition becomes TRUE. This loop allows you to use a condition without using a IF Statement within a loop.

Assume that, you have few numbers in A1:A12. Now what you wish to do is, add 3 numbers to each values A1:A12. The loop continues while a certain condition is TRUE. Well, you need to add 3 numbers to every cell's values then use the below code:

Sub dwloop()
   Dim i As Integer
   i = 1
   Do While Cells(i, 1).Value > 0
      Cells(i, 2).Value = Cells(i, 1).Value + 3
      i = i + 1
   Loop
End Sub

So it is clear that, while you need to apply a condition (IF Statement) within a Loop, but you are not allowed to use IF Statement in the loop, then choose Do ... While loop. Because without condition it will not work.



Do ... Until ... Loop:

This is another type of Loop in Excel VBA. It continues the Loop while a condition becomes FALSE. This loop allows you to use a condition without using a IF Statement within a loop.

Assume that, you have few numbers in A1:A12. Now what you wish to do is, add 3 numbers to each values A1:A12. The loop continues while a certain condition is FALSE . Well, you need to add 3 numbers to every cell's values then use the below code:

Sub dwloop()
   Dim i As Integer
   i = 1
   Do Until Cells(i, 1).Value = ""
      Cells(i, 2).Value = Cells(i, 1).Value + 3
      i = i + 1
   Loop
End Sub

So it is clear that, while you need to apply a condition (IF Statement) within a Loop, but you are not allowed to use IF Statement in the loop, then choose Do ... Until loop. Because without condition it will not work.



While ... Wend:

From the above For Next loop, We know the times of running the VBA Loop. But what if we don't know how many times will run the loop? In this case you need to use While ... Wend loop with a condition.

Assume that, you have few numbers in A1:A12. Now what you wish to do is, add 3 numbers to each values A1:A12. Below are the sample data:

 Image 4: Sample data

To do this just use the below code:

Sub wwl()
Dim i As Integer
i = 2
While i < 13
    Cells(i, 2) = Cells(i, 1) + 3
    i = i + 1
Wend
End Sub

While you know nothing about how many times your loop will run, then use a fixed value with < (Less than) condition on While loop to run it. In the above code, the loop will run continuously unless i < 13.

|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw