For Next loop in VBA

For Next Loop

The ”For Next” loop is used for a fixed number of times. It works by implementing the loop for the specified number of times. In this, the user specifies how many times they want the loop to run and need to define what they want the macro to do each time the for next loop is run.

Syntax

For counter = start To end [ Step step ]
[ statements ]
,
Next [ counter ]

Parameter

counter (required) – This parameter represents the loop to counter the variable.

start (required)- It represents the starting or first value for the counter.

end (required)- It represents the ending or last value for the counter.

step (optional)- This parameter represents the value that ‘counter’ is incremented where each value passes through the loop. It can be either a positive or a negative number. If the step parameter is not specified, it will default to an increment of 1 so that each pass through the loop increases counter by 1.

statement (required)- It represents the statements of code to execute each pass through the loop.

Example 1: Write a macro using For Next loop in VBA to print a number from 1 to 20.

Sub For_Next_Example1()

‘In the below code, rownum is a variable, and it will run 20 times till value 20 is reached increasing by 1 each time.

Dim RowNum As Integer
    'Will run the loop starting from 1 to 20
    For RowNum = 1 To 20
        Worksheets("Sheet1").Cells(RowNum, 1).Value = RowNum
    'Incrementing the RowNum value by 1
    Next RowNum
End Sub

Output

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Example 2: Write a Macro using For Next loop in VBA to print the tables from 1 to 10

Sub For_Next_Example2()
Dim rCount As Byte, cCount As Byte
    'Row Counter
    For rCount = 1 To 10
        'Column Counter
        For cCount = 1 To 10
            'Jumping to Next column
            Cells(rCount, cCount) = cCount & "*" & rCount & "=" & rCount * cCount
        'Jumping to Next row
        Next
    Next
End Sub

Output

1*1=1	2*1=2	3*1=3	4*1=4	5*1=5	6*1=6	7*1=7	8*1=8	9*1=9	10*1=10
1*2=2	2*2=4	3*2=6	4*2=8	5*2=10	6*2=12	7*2=14	8*2=16	9*2=18	10*2=20
1*3=3	2*3=6	3*3=9	4*3=12	5*3=15	6*3=18	7*3=21	8*3=24	9*3=27	10*3=30
1*4=4	2*4=8	3*4=12	4*4=16	5*4=20	6*4=24	7*4=28	8*4=32	9*4=36	10*4=40
1*5=5	2*5=10	3*5=15	4*5=20	5*5=25	6*5=30	7*5=35	8*5=40	9*5=45	10*5=50
1*6=6	2*6=12	3*6=18	4*6=24	5*6=30	6*6=36	7*6=42	8*6=48	9*6=54	10*6=60
1*7=7	2*7=14	3*7=21	4*7=28	5*7=35	6*7=42	7*7=49	8*7=56	9*7=63	10*7=70
1*8=8	2*8=16	3*8=24	4*8=32	5*8=40	6*8=48	7*8=56	8*8=64	9*8=72	10*8=80
1*9=9	2*9=18	3*9=27	4*9=36	5*9=45	6*9=54	7*9=63	8*9=72	9*9=81	10*9=90
1*10=10	2*10=20	3*10=30	4*10=40	5*10=50	6*10=60	7*10=70	8*10=80	9*10=90	10*10=100

For Next Double Loop: In this we use one for next inside the other.

Example 3: Write a macro using for next double loop, to print a 4 * 4 square.

Sub For_Next_Example3()
   Dim Row As Integer
   Dim Col As Integer
   'The first loop and initializing the row from 2 to 5
    For Row = 2 To 5
        'The first loop and initializing the col from 3 to 6
        For Col = 3 To 6
            Cells(Row, Col) = "*"
      Next Col
   Next Row
End Sub

Output

*	*	*	*
*	*	*	*
*	*	*	*
*	*	*	*