×

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=12*1=23*1=34*1=45*1=56*1=67*1=78*1=89*1=910*1=10
1*2=22*2=43*2=64*2=85*2=106*2=127*2=148*2=169*2=1810*2=20
1*3=32*3=63*3=94*3=125*3=156*3=187*3=218*3=249*3=2710*3=30
1*4=42*4=83*4=124*4=165*4=206*4=247*4=288*4=329*4=3610*4=40
1*5=52*5=103*5=154*5=205*5=256*5=307*5=358*5=409*5=4510*5=50
1*6=62*6=123*6=184*6=245*6=306*6=367*6=428*6=489*6=5410*6=60
1*7=72*7=143*7=214*7=285*7=356*7=427*7=498*7=569*7=6310*7=70
1*8=82*8=163*8=244*8=325*8=406*8=487*8=568*8=649*8=7210*8=80
1*9=92*9=183*9=274*9=365*9=456*9=547*9=638*9=729*9=8110*9=90
1*10=102*10=203*10=304*10=405*10=506*10=607*10=708*10=809*10=9010*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

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

Related Topics

Excel VBA DateAdd Function

The DateAdd function in VBA adds a time interval to a supplied date and/or time and returns the resultant date/time. Syntax Dateadd (Interval, Number, Date) Parameter Interval (required) – This parameter a string specifying the interval to be...

1 minute read.

Excel VBA CInt Function

VBA CInt Function: The VBA Cint function converts the specified expression into an Integer. Syntax Cint (Expression) Parameter Expression (required) – This parameter represents the expression that you want to convert to an Integer wherein...

1 minute read.

Procedures in VBA

Procedures in VBA A procedure is a block of statements or units of computer code that performs some action. It is enclosed with a declaration statement, and its primary purpose is to carry out...

3 minutes read.

Excel VBA UCase Function

The UCase function in VBA converts a String into upper case text. Syntax UCase (String) Parameter String (required) – This parameter represents the string that you want to convert to upper case. Return This function returns a string...

1 minute read.

Excel VBA LCase Function

VBA LCase Function: The LCase function in VBA converts the given String into lower case text. Syntax LCase (String) Parameter String (required)- This parameter represents the text string that you want to convert to...

1 minute read.

VBA Not Equal Operator

What is VBA Not Equal Operator? VBA Not Equal binary operator (“<>”) is a logical function that is used to check if the specified values are not equal or not. This...

5 minutes read.

Excel VBA Now Function

Excel VBA Now Function: The Now function in VBA returns the current date and time.  Syntax Now () Parameter NA Return This function returns the current date and time.  Example 1 Sub NowFunction_Example1() 'It will return...

1 minute read.

Excel VBA : With End-with Constructs

With End-with Constructs The With-End With construct enables the user to perform multiple operations on a single object. If you are going to perform several different actions on the same object and typing the same...

4 minutes read.

Excel VBA: IF…..THEN …ELSE Statement

VBA : IF…..THEN …ELSE Statement: This function enables you to check one condition and, based on that, then run one of the two statement blocks present. If the ‘IF’ condition...

2 minutes read.

Excel VBA Hour Function

The hour function in VBA returns the hour element for the specified time. Syntax Hour (Time) Parameter Time (required) – This parameter represents the time. Return This function returns the hour element for the specified time. Example 1 Sub HourFunction_Example1() ...

1 minute read.

Excel VBA Month Function

The Mont function in VBA returns the month number for the specified date. Syntax Month (Date) Parameter Time (required) – This parameter represents the date. Return This function returns the month number for the specified date. Example 1 Sub MonthFunction_Example1() ...

1 minute read.

Excel VBA IsNumeric Function

VBA IsNumeric Function: The IsNumeric function in VBA returns a Boolean value showing whether the specified Expression contains a numeric value or not. Syntax IsNumeric (Expression) Parameter Expression (required)- This parameter represents the variant that...

1 minute read.

VBA Dim

What is Dim? DIM or Dimension or Declare in Memory is a keyword that is used in VBA to declare a variable with the different data types (Integer, String, variable, Boolean, Double, etc.)...

7 minutes read.

Excel VBA User-Defined Functions

User-Defined Functions One of the advantages of VBA is that you can create your own functions using macros. These functions can be called and used as other functions in excel and use them. You can...

3 minutes read.

Excel VBA Array

Introduction to VBA Array An array is a type of variable that holds more than one piece of data. In VBA, you can refer to a specific variable (element) of an array by using...

5 minutes read.

Userform Events

What are Events? Anything you do in to trigger an excel file is an event (an action). Example: If you want a greeting message ‘Good Day’ whenever an excel file is...

7 minutes read.

Miscellaneous Exercise of conditional statements and Loop

We have the already worked with syntax and examples of conditional statements and loops in the previous tutorials. In this tutorial, we will learn how to work with both together.  We will explain...

3 minutes read.

Scope in Visual Basics

Definition of Scope The scope of any programming language implies the area of code where the variables will be identified, accessed, and used. Every variable has a scope associated with it. The scope of...

4 minutes read.

Excel VBA Choose Function

VBA Choose Function: The Choose function in VBA chooses function selects the corresponding value from a list of arguments depending as per the specified index. Syntax Choose (Index, [Choice-1], [Choice-2], ...) Parameter Index (required) – This...

2 minutes read.

VBA TimeSerial Function

The TimeSerial function in VBA returns a Time for the specified hour, minute, and second. Syntax TimeSerial (Hour, Minute, Second) Parameter Hour (required) – This parameter represents an integer (0 to 23), signifying the hour of the time. Minute...

2 minutes read.