How to use For Next Loop in Excel VBA?

In this blog explains how to use the FOR…NEXT statement to create a FOR loop in VBA with syntax and examples.

Description

The FOR…NEXT statement is used to create a FOR loop. You can execute code a fixed number of times.

Suppose we have to do lots of work in Excel i.e consolidate data from multiple worksheets in a single worksheet, import data into excel from SQL Server, import data into excel from MS Access, making PowerPoint Presentation, Creating Word Document so we use the loop for work fast.

Syntax

How to create a FOR Loop in VBA using with FOR…NEXT statement

        For Counter = Start to End [Step Increment]

                VBA Statement

        Next Counter 

Example of For Loop

Sub Loop_Counter()

        For Counter = 1 to 5

                MsgBox “Counter Value-“ & Counter

        Next Counter

 End Sub

This loop executes 5 times and MsgBox display 5 times of counter value.

The counter value increases each executes time.

Counter Value-1

Counter Value-2

Counter Value-3

Counter Value-4

Counter Value-5

 

Step Increment

[Step Increment] – This is an optional argument for For Next Loop. Step Increment is used for increment value in the counter by one, two, and three and more. If we mention as Step 2 then counter value increased by 2 in each time.

Sub Loop_Counter()

        For Counter = 1 to 10 Step 2

                MsgBox “Counter Value-“ & Counter

        Next Counter

 End Sub

The counter value increased by 2 in each executes time.

Counter Value-1

Counter Value-3

Counter Value-5

Counter Value-7

Counter Value-9

 

How to use reverse For Loop in VBA?

Reverse For Loop run maximum counter value to minimum counter value. If we want to use reverse For Loop then we have to mention as Step –1, Step –2, and Step –3. If we mention as Step –2 then counter value decreased by 2 in each time.

        For Counter = End to Start [Step Increment]

                VBA Statement

        Next Counter

 

Example of For Loop Reverse

Sub Loop_Reverse()

        For Counter = 10 to 1 Step –1

                MsgBox “Counter Value-“ & Counter

        Next Counter

End Sub

This loop executes 10 times and MsgBox display 10 times of counter value.

The counter value decreases each executes time.

Counter Value-10

Counter Value-9

Counter Value-8

Counter Value-7

Counter Value-6

Counter Value-5

Counter Value-4

Counter Value-3

Counter Value-2

Counter Value-1

 

Sub Loop_Reverse()

        For Counter = 10 to 1 Step –2

                MsgBox “Counter Value-“ & Counter

        Next Counter

End Sub

The counter value decreased by 2 in each executes time.

Counter Value-10

Counter Value-8

Counter Value-6

Counter Value-4

Counter Value-2

 

How to create Nested FOR Loop in VBA using with FOR…NEXT statement

As per need, we use multiple loops in our programming. We may use loop a within a loop.

for next loop in vba

 

Example for Nested Loop

Sub Nested_FOR_Loop()

         Dim Counter1 As Integer, Counter2 As Integer

        For Counter1 = 1 To 3

                 For Counter2 = 1 To 5

                         MsgBox “Counter1 Value : “ & Counter1 & “Counter2 Value : “ & Counter2

                 Next Counter2

         Next Counter1

 End Sub

Print Value:

Counter1 Value: 1 – Counter2 Value: 1

Counter1 Value: 1 – Counter2 Value: 2

Counter1 Value: 1 – Counter2 Value: 3

Counter1 Value: 1 – Counter2 Value: 4

Counter1 Value: 1 – Counter2 Value: 5

Counter1 Value: 2 – Counter2 Value: 1

Counter1 Value: 2 – Counter2 Value: 2

Counter1 Value: 2 – Counter2 Value: 3

Counter1 Value: 2 – Counter2 Value: 4

Counter1 Value: 2 – Counter2 Value: 5

Counter1 Value: 3 – Counter2 Value: 1

Counter1 Value: 3 – Counter2 Value: 2

Counter1 Value: 3 – Counter2 Value: 3

Counter1 Value: 3 – Counter2 Value: 4

Counter1 Value: 3 – Counter2 Value: 5

Leave a Reply

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