Excel Tip > Excel Macros and VBA > Using Loops > 7 Examples of For Loops in Microsoft Excel VBA

7 Examples of For Loops in Microsoft Excel VBA

Fb-Button

If you are writing a VBA program & you want to execute the same task multiple times then you can do this by using VBA for loops. VBA is a sequential programming language. Each line of code gets executed from top to bottom until there are no more lines of code to read. If you want to go back & perform a task, then you have to force by using macro code. You get results with loop.

The For Loop in VBA is one of the most common types of loop. The For loop has two forms: For Next and For Each In Next. The For loop is typically used to move sequentially through a list of items or numbers. To end the For loop at any given point, we can use the exit statement. For Loop will go round and round until it meets the end condition. Once the end condition is met, the programming flow will continue downward, in its natural direction.

 

The For … Next loop has the following syntax:

For counter = start_counter To end_counter

'Do something here (your code)

Next counter

 

We are actually creating a loop that uses variable counter as the ‘time keeper’ of the loop. We set it to a value equal to start_counter at the beginning of the loop and then increment it by 1 during each loop till it meets the end condition. The loop will execute till the time the value of the counter becomes equal to end_counter. The loop executes for the last time when both the above values match and then the loop stops.

All of the above might be confusing for some people, so let’s gear up & start learning For Loop in excel with few examples.

  • Create a new excel workbook then save it with the extension .xlsm
  • To launch Visual Basic editor screen, use ALT + F11
  • Insert New Module
  • Copy the below code in the VB standard module

 

img1

 

Loop 1 (Displaying number with msgbox)

Sub Loop1()
Dim StartNumber As Integer
Dim EndNumber As Integer
EndNumber = 5
    
    For StartNumber = 1 To EndNumber
        MsgBox StartNumber & " is " & "Your StartNumber"
    Next StartNumber

End Sub

img2

 

Code Explanation:

  • The VBA code needs you to assign value to StartNumber, where EndNumber are variables that are declared as integers as the starting point for your loop
  • These values can be any number & we have EndNumber as 5
  • StartNumber is started at 1
  • For StartNumber = 1 To EndNumber means code will start from 1 (StartNumber) to 5 (EndNumber)
  • MsgBox StartNumber & " is " & "Your StartNumber" will display the following message box

 

img3

 

Loop2 (Fill Values)

Sub Loop2()
'Fills cells A1:A56 with values of X by looping' --- Comment
'Increase value of X by 1 in each loop' --- Comment

Dim X As Integer
    For X = 1 To 56
        Range("A" & X).Value = X
    Next X
End Sub

 

img4

 

Code Explanation:

  • We have assigned X as an integer
  • For X = 1 to 56; this will start with 1 and continue till 56 with an increment of 1 each time
  • Range("A" & X).Value = X; this line will store the value of X and will pass to range A1 to A56

 

img5

 

Loop3 (Fill cells with background color)

Sub Loop3()
' Fills cells B1:B56 with the 56 background colours' --- Comment
Dim X As Integer
    For X = 1 To 56
        Range("B" & X).Select
        With Selection.Interior
            .ColorIndex = X
            .Pattern = xlSolid
        End With
    Next X
End Sub

 

img6

 

Code Explanation:

  • We have assigned X as an integer
  • For X = 1 to 56 will start with 1 and continue till 56 with an increment of 1 each time
  • Range("B" & X).Select; this line will store the value of X & select the cell B1 till B56
  • The next 4 lines i.e. With Selection.Interior will select interior colorindex & take the value from X of that colorindex such that 1 belongs to color black; 2 belongs to color white; 3 for red & so on

 

img7

 

Loop 4 (Fill Values with increment of 2)

By default, the Step value is forward 1, however it can be set to a number more than 1.

Sub Loop4()
' Fills every second cell from C1:C50 with values of X' --- Comment
Dim X As Integer
    For X = 1 To 50 Step 2
        Range("C" & X).Value = X
    Next X
End Sub

 

img8

 

Code Explanation:

  • We have assigned X as an integer
  • For X = 1 to 50 Step 2; this will start with 1 in X till 50 with an increment of 2 each time
  • Range("C" & X).Value = X; this line will store the value of X and will pass to range C1 to C50

 

img9

 

Loop 5 (VBA For Loop in Reverse with STEP Instruction)

It is not necessary that counter in the For loop will only move from low to higher values; instead, For loop can run backwards, too i.e. high to lower values.

Even though the Step value is forward 1 by default, however, it can be set to a number in reverse order.

Sub Loop5()
' Fills cells from D1:D50 with values of X' --- Comment
' In this case X decreases by 1' --- Comment
Dim X As Integer, Row As Integer

Row = 1
    
    For X = 50 To 0 Step -1
        Range("D" & Row).Value = X
        Row = Row + 1
    Next X

End Sub

 

img10

 

Code Explanation:

  • We have assigned X & Row as integer
  • Row contains value 1
  • For X = 50 to 0 Step -1; this will start from 50 with decrement by 1 in X till 0
  • Range("D" &Row).Value = X; this line will store the value of X and will pass to range D1 to D50

 

img11

 

Loop 6 (Fills every second cell in Reverse with STEP-2)

In the above For loop example, we can use the Step and order to see if the For loop works in forward or backward direction.

Sub Loop6()
' Fills every second cell from E1:E100 with values of X' --- Comment
' In this case X decreases by 2' --- Comment
Dim X As Integer, Row As Integer
Row = 1
    For X = 100 To 0 Step -2
        Range("E" & Row).Value = X
        Row = Row + 2
    Next X
End Sub

 

img12

 

Code Explanation:

  • We have assigned X & Row as integer
  • Row contains value 1
  • For X = 100 to 0 Step -2; this will start from 100 with decrement by 2 in X till 0
  • Range("E" &Row).Value = X; this line will store the value of X and will pass to range E1 to E100

 

img13

 

Loop 7 (For Loop with IF condition: Fills cells starting from specific cell)

This will fill the cells from cell F11 with value 11 till X meets the IF condition

Sub Loop7()
' Starts to fill cells F11:F100 with values of X' --- Comment
' This will exit from the loop after 50' --- Comment

Dim X As Integer

    For X = 11 To 100
        Range("F" & X).Value = X
            If X = 50 Then
                MsgBox ("Bye Bye")
                Exit For
            End If
    Next X

End Sub

 

img14

 

Code Explanation:

  • We have assigned X as an integer
  • For X = 11 to 100; will start from 11 with increment by 1 in X till the condition meets
  • Range("F" &X).Value = X; this line will store the value of X and will pass to range F11 till the condition meets

 

img15

 

  • After entering the value 50 in cell F50, the following message box will be displayed

 

img16

 

Conclusion: With the above 7 examples, we can apply For loop in our regular or any automation part.

 

image 48
 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 

 

Previous
Next

Comments

  1. Bogdangvx

    ??????????? ??? ???????!
    ?????????? ?????? ???????? ??????? ?? ?????? ??? ???? ? ?????.???? ??????????? ??? «??????????» ???????? 10 ??? ?? ????? ???? ????????? ? ????????.
    ?? ????? ?????????? ??? ???????????? ?????????? ?????,??????? ?????????? ???????????,??????? ?????? ?? ??????????? ??????,?????????? ????? ? ??? ? ?????????? ?????????? ?? ??????,? ??????????????? ???????? ? ??????????? ???????????
    ????? ????????? ?????????? ????????? ?? ????? ?????
    http://www.space-prod.com/index/8-9728
    http://lastmoon.org/forum/index.php?action=profile;u=67470
    https://hulafrog2.com/burlington-winchester-ma/register-business/?er=captcha&email=b.l.i.zko.y.a.n2.017%40gmail.com&bname=Bogdanxnt&fn=Bogdanxnt&busi=Bogdanxnt&web=https%3A%2F%2Fdrive.google.com%2Fopen%3Fid%3D1PoE0qZwcokJRhjHqUDuNciFhoeYgqTh0&fb=https%3A%2F%2Fdrive.google.com%2Fopen%3Fid%3D1PoE0qZwcokJRhjHqUDuNciFhoeYgqTh0&phone=84463529756&add1=Minsk&add2=Minsk&town=Minsk&state=MN&zip=151414&cat=&subcat=&about=%D0%94%D0%BE%D0%B1%D1%80%D1%8B%D0%B9%20%D0%B4%D0%B5%D0%BD%D1%8C%20%D0%B4%D0%B0%D0%BC%D1%8B%20%D0%B8%20%D0%B3%D0%BE%D1%81%D0%BF%D0%BE%D0%B4%D0%B0%3Ca%20href%3Dhttps%3A%2F%2Fstekloelit.by%3E%21%3C%2Fa%3E%20%0D%0A%D0%9F%D1%80%D0%B5%D0%B4%D0%BB%D0%B0%D0%B3%D0%B0%D0%B5%D0%BC%20%D0%92%D0%B0%D1%88%D0%B5%D0%BC%D1%83%20%D0%B2%D0%BD%D0%B8%D0%BC%D0%B0%D0%BD%D0%B8%D1%8E%20%D0%B8%D0%B7%D0%B4%D0%B5%D0%BB%D0%B8%D1%8F%20%D0%B8%D0%B7%20%D1%81%D1%82%D0%B5%D0%BA%D0%BB%D0%B0%20%D0%B4%D0%BB%D1%8F%20%D0%B4%D0%BE%D0%BC%D0%B0%20%D0%B8%20%D0%BE%D1%84%D0%B8%D1%81%D0%B0.%D0%9D%D0%B0%D1%88%D0%B0%20%D0%BE%D1%80%D0%B3%D0%B0%D0%BD%D0%B8%D0%B7%D0%B0%D1%86%D0%B8%D1%8F%20%D0%9E%D0%9E%D0%9E%20%C2%AB%D0%A1%D0%A2%D0%95%D0%9A%D0%9B%D0%9E%D0%AD%D0%9B%D0%98%D0%A2%C2%BB%20%D1%80%D0%B0%D0%B1%D0%BE%D1%82%D0%B0%D0%B5%D1%82%2010%20%D0%BB%D0%B5%D1%82%20%D0%BD%D0%B0%20%D1%80%D1%8B%D0%BD%D0%BA%D0%B5%20%D1%8D%D1%82%D0%BE%D0%B9%20%D0%BF%D1%80%D0%BE%D0%B4%D1%83%D0%BA%D1%86%D0%B8%D0%B8%20%D0%B2%20%D0%91%D0%B5%D0%BB%D0%B0%D1%80%D1%83%D1%81%D0%B8.%20%0D%0A%D0%9C%D1%8B%20%D0%BC%D0%BE%D0%B6%D0%B5%D0%BC%20%D0%BF%D1%80%D0%B5%D0%B4%D0%BB%D0%BE%D0%B6%D0%B8%D1%82%D1%8C%20%D0%92%D0%B0%D0%BC%20%3Ca%20href%3Dhttps%3A%2F%2Fdrive.google.com%2Fopen%3Fid%3D1PoE0qZwcokJRhjHqUDuNciFhoeYgqTh0%3E%D0%9C%D0%B5%D0%B6%D0%BA%D0%BE%D0%BC%D0%BD%D0%B0%D1%82%D0%BD%D1%8B%D0%B5%20%D1%81%D1%82%D0%B5%D0%BA%D0%BB%D1%8F%D0%BD%D0%BD%D1%8B%D0%B5%20%D0%B4%D0%B2%D0%B5%D1%80%D0%B8%3C%2Fa%3E%2C%3Ca%20href%3Dhttps%3A%2F%2Fdrive.google.com%2Fopen%3Fid%3D1PoE0qZwcokJRhjHqUDuNciFhoeYgqTh0%3E%D0%9E%D1%84%D0%B8%D1%81%D0%BD%D1%8B%D0%B5%20%D1%81%D1%82%D0%B5%D0%BA%D0%BB%D1%8F%D0%BD%D0%BD%D1%8B%D0%B5%20%D0%BF%D0%B5%D1%80%D0%B5%D0%B3%D0%BE%D1%80%D0%BE%D0%B4%D0%BA%D0%B8%3C%2Fa%3E%2C%3Ca%20href%3Dhttps%3A%2F%2Fdrive.google.com%2Fopen%3Fid%3D1PoE0qZwcokJRhjHqUDuNciFhoeYgqTh0%3E%D0%94%D1%83%D1%88%D0%B5%D0%B2%D1%8B%D0%B5%20%D0%BA%D0%B0%D0%B1%D0%B8%D0%BD%D1%8B%20%D0%B8%D0%B7%20%D0%B7%D0%B0%D0%BA%D0%B0%D0%BB%D0%B5%D0%BD%D0%BD%D0%BE%D0%B3%D0%BE%20%D1%81%D1%82%D0%B5%D0%BA%D0%BB%D0%B0%3C%2Fa%3E%2C%3Ca%20href%3Dhttps%3A%2F%2Fdrive.googl&hours=%D0%94%D0%BE%D0%B1%D1%80%D1%8B%D0%B9%20%D0%B4%D0%B5%D0%BD%D1%8C%20%D0%B4%D0%B0%D0%BC%D1%8B%20%D0%B8%20%D0%B3%D0%BE%D1%81%D0%BF%D0%BE%D0%B4%D0%B0%3Ca%20href%3Dhttps%3A%2F%2Fstekloelit.by%3E%21%3C%2Fa%3E%20%0D%0A%D0%9F%D1%80%D0%B5%D0%B4%D0%BB%D0%B0%D0%B3%D0%B0%D0%B5%D0%BC%20%D0%92%D0%B0%D1%88%D0%B5%D0%BC%D1%83%20%D0%B2%D0%BD%D0%B8%D0%BC%D0%B0%D0%BD%D0%B8%D1%8E%20%D0%B8%D0%B7%D0%B4%D0%B5%D0%BB%D0%B8%D1%8F%20%D0%B8%D0%B7%20%D1%81%D1%82%D0%B5%D0%BA%D0%BB%D0%B0%20%D0%B4%D0%BB%D1%8F%20%D0%B4%D0%BE%D0%BC%D0%B0%20%D0%B8%20%D0%BE%D1%84%D0%B8%D1%81%D0%B0.%D0%9D%D0%B0%D1%88%D0%B0%20%D0%BE%D1%80%D0%B3%D0%B0%D0%BD%D0%B8%D0%B7%D0%B0%D1%86%D0%B8%D1%8F%20%D0%9E%D0%9E%D0%9E%20%C2%AB%D0%A1%D0%A2%D0%95%D0%9A%D0%9B%D0%9E%D0%AD%D0%9B%D0%98%D0%A2%C2%BB%20%D1%80%D0%B0%D0%B1%D0%BE%D1%82%D0%B0%D0%B5%D1%82%2010%20%D0%BB%D0%B5%D1%82%20%D0%BD%D0%B0%20%D1%80%D1%8B%D0%BD%D0%BA%D0%B5%20%D1%8D%D1%82%D0%BE%D0%B9%20%D0%BF%D1%80%D0%BE%D0%B4%D1%83%D0%BA%D1%86%D0%B8%D0%B8%20%D0%B2%20%D0%91%D0%B5%D0%BB%D0%B0%D1%80%D1%83%D1%81%D0%B8.%20%0D%0A%D0%9C%D1%8B%20%D0%BC%D0%BE%D0%B6%D0%B5%D0%BC%20%D0%BF%D1%80%D0%B5%D0%B4%D0%BB%D0%BE%D0%B6%D0%B8%D1%82%D1%8C%20%D0%92%D0%B0%D0%BC%20%3Ca%20href%3Dhttps%3A%2F%2Fdrive.google.com%2Fopen%3Fid%3D1PoE0qZwcokJRhjHqUDuNciFhoeYgqTh0%3E%D0%9C%D0%B5%D0%B6%D0%BA%D0%BE%D0%BC%D0%BD%D0%B0%D1%82%D0%BD%D1%8B%D0%B5%20%D1%81%D1%82%D0%B5%D0%BA%D0%BB%D1%8F%D0%BD%D0%BD%D1%8B%D0%B5%20%D0%B4%D0%B2%D0%B5%D1%80%D0%B8%3C%2Fa%3E%2C%3Ca%20href%3Dhttps%3A%2F%2Fdrive.google.com%2Fopen%3Fid%3D1PoE0qZwcokJRhjHqUDuNciFhoeYgqTh0%3E%D0%9E%D1%84%D0%B8%D1%81%D0%BD%D1%8B%D0%B5%20%D1%81%D1%82%D0%B5%D0%BA%D0%BB%D1%8F%D0%BD%D0%BD%
    http://www.akbard.kz/festival/user/Bogdanlbs/
    http://banglagamer.com/member.php?396757-Mihaeluhp

    Reply ↓
  2. Baseam

    Please , i have this problem, my code
    for i = 1 to 100
    if worksheets("sheet1").cells(i,1).value = i ------------this dont work
    when i change i to 1 it works like this
    if worksheets("sheet1").cells(i,1).value = 1-----------work
    i try convert the cell covert the every value no way , thanks

    Reply ↓
    • Manish Singh

      Hi Baseam,

      Sub forsol()

For i = 1 To 100
If Worksheets("sheet1").Cells(i, 1).Value = i Then
 Worksheets("sheet1").Cells(i, 2).Value = True 'put your code
End If
Next i

End Sub

      let me know if it works for you.

      Reply ↓
    • Exceltip

      Thanks, V S GAUTHAMI... There's a wave of these simple VBA explanations coming. But if you want any specific term to understand, you can mention it here.

      Reply ↓
  6. Mitchel

    Hello,

    Very good blog with simple & smart examples on how to use For Loops. I have bookmarked this link for future references.

    Very much appreciated.

    Mitchel

    Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Excel Tips

Categories

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.