For...Next Loop Syntax in VBA

The Microsoft Excel FOR...NEXT statement is used to create a FOR loop so that you can execute VBA code a fixed number of times.

The FOR...NEXT statement is a built-in function in Excel that is categorized as a Logical Function. It can be used as a VBA function (VBA) in Excel.

Syntax

FOR counter = start TO end [Step increment]

   {...statements...}

NEXT [counter];

Note: counter can be changed using “STEP”.

Parameters or Arguments:

 

Parameter/Arguments	Description
counter	Required in the For statement. Numeric variable. The control variable for the loop. For more information, see Counter Argument later in this topic.
datatype	Optional. Data type of counter.
start	Required. Numeric expression. The initial value of counter.
end	Required. Numeric expression. The final value of counter.
step	Optional. Numeric expression. The amount by which counter is incremented each time through the loop.
statements	Optional. One or more statements between For and Next that run the specified number of times.
Continue For	Optional. Transfers control to the next loop iteration.
Exit For	Optional. Transfers control out of the For loop.
Next	Required. Terminates the definition of the For loop

Click Here for examples:

For.. Next Loop in VBA with example

The Microsoft Excel FOR...NEXT statement is used to create a FOR loop so that you can execute VBA code a fixed number of times.

The FOR...NEXT statement is a built-in function in Excel that is categorized as a Logical Function. It can be used as a VBA function (VBA) in Excel.

Single Loop:

Below is the example of single For …Loop :

Sub Singleloopexample()
   Dim i As Integer
  For i = 1 To 5
    ThisWorkbook.Worksheets("Output").Cells(i + 1, 1).Value = "LoopIncrement." & i
  Next i
 End Sub

 Output:

Single Loop  changing increment:

By using “STEP” parameter the counter value can be Incremented & Decremented .

#Positive Increment:

Sub Singleloopexample_step()
 Dim i As Integer
 For i = 1 To 5 Step 2
   ThisWorkbook.Worksheets("Output").Cells(i + 1, 1).Value = "LoopIncrement." & i
 Next i

End Sub

Output:

 

#Negative Increment

Sub NegativeIncrement()
 Dim i As Integer
   j = 2
   For i = 7 To 1 Step -2
     
    ThisWorkbook.Worksheets("Output").Cells(j, 1).Value = "LoopIncrement." & i
    j = j + 1
   
   Next i
End Sub

Output:

 

Nested For loop:

Below is the few example of nested for loop.

Double For……. Loop :

In below example two for loop being used. Outer for loop being controlled by “i” and inner loop controlled by “j” .

Below is the

Sub Doubleloop()
Dim i As Integer, j As Integer
For i = 1 To 6
     For j = 1 To 2
         ThisWorkbook.Worksheets("Output").Cells(i + 1, j).Value = "LoopIncrement." & i & "*" & j
     Next j
Next i
End Sub

Output:

As Double  For ... Loop , we can use Triple For Loop also.

You May like to Read this also

 

How Do While Loop works in VBA

Do... While Loop Statement:

Repeats a block of statements while a condition is True or until a condition becomes True.

Syntax:

Do [{While | Until} condition]

[statements]

[Exit Do]
[statements]

Loop
Or, you can use this syntax:
Do
[statements]
[Exit Do]
[statements]

Loop [{While | Until} condition]

The Do Loop statement syntax has these parts:

Part	Description
condition	Optional. Numeric expression or string expression that is True or False. If condition is Null, condition is treated as False.
statements	One or more statements that are repeated while, or until, condition is True.

 

Remarks:

Any number of Exit Do statements may be placed anywhere in the Do…Loop as an alternate way to exit a Do…Loop.Exit Do is often used after evaluating some condition, for example, If…Then, in which case the Exit Do statement transfers control to the statement immediately following the Loop.

When used within nested Do…Loop statements, Exit Do transfers control to the loop that is one nested level above the loop where Exit Do occurs.

Example:

This example shows how Do...Loop statements can be used. The inner Do...Loop statement loops 10 times, sets the value of the flag to False, and exits prematurely using the Exit Do statement. The outer loop exits immediately upon checking the value of the flag.

 

Dim Check, Counter Check = True: Counter = 0    ' Initialize variables. Do    ' Outer loop.     Do While Counter < 20    ' Inner loop.         Counter = Counter + 1    ' Increment Counter.         If Counter = 10 Then    ' If condition is True.             Check = False    ' Set value of flag to False.             Exit Do    ' Exit inner loop.         End If     Loop Loop Until Check = False    ' Exit outer loop immediately.

How to Copy data into a Text box through Macro

TextBox :

TextBox is used to display data, edit data, etc in the rectangular box. The Textbox can be linked to a Worksheet Cell. The TextBox can have static and dynamic value. When data or text is static in the TextBox field, it represents the read only information. It can be used on the WorkSheet or UserForm. You can see how it works and more details about ActiveX TextBox Control on the UserForm or Worksheet in the following chapter.

The below code is useful if Textbox is already available .

One way to create Textbox is Go to Insert Tab -> Shapes -> Basic Shapes ->

 

 

Sub textbox1()

Dim Textbox As String

Textbox = ThisWorkbook.Worksheets("Main").Cells(1, 1).Value

ActiveSheet.Shapes("Textbox 1").TextFrame.Characters.Text = Textbox

ActiveSheet.Shapes.Range(Array("TextBox 1")).Select

    With Selection.ShapeRange.TextFrame2.TextRange.Font

        .NameComplexScript = "Algerian"

        .NameFarEast = "Algerian"

        .Name = "Algerian"

    End With

    Selection.ShapeRange.TextFrame2.TextRange.Font.Size = 18

End Sub

 

 

Read More

 

How to create Drop down list in Excel

Excel drop-down list is used to enter data in a spreadsheet from a pre-defined list. The main purpose of using drop down lists in Excel is to limit the number of choices available for the user. Apart from that, a dropdown prevents spelling mistakes and it will make automation task easy as number of input is predefined.

Below is the two ways to create simple drop down

Comma separated Drop Down list:

In Below example I am trying to add drop down list in column B only highlighted cells  .

 Please follow the below steps:

1.       Select all required range or non-contiguous cells. Then  Go to Data Tab .

2.       Click on Data Validation icon drop down. click on the Data Validation

3.       In the Data validation window on the setting tab:

Ø  In the Allow drop down select “List” and make sure “In-Cell dropdown”  check box should be checked

Ø  In the Source Text box give the values which you want to be in drop down as comma separated.

 

Ø  Click ok .That’s what you are looking for

 

Get The values from different Cells:

If you do not want to write the Values directly to Source  as we did above , we can get those from other cells like below .

1.       Write the expected values in any column in the same worksheet or different. In my example I want Column F values to be in dropdown list for Column B.

 

2.       Select all required range or non-contiguous cells. Then  Go to Data Tab .

3.       Click on Data Validation icon drop down. click on the Data Validation

4.       In the Data validation window on the setting tab:

Ø  In the Allow drop down select “List” and make sure “In-Cell dropdown”  check box should be checked

Ø  In the Source Text box  click on the button situated at right hand it will allow you to select the range as shown in below . Once data selected click on the button again it will bring you to data validation Window

 

Ø  Click ok .That’s what you are looking for

 

Read More

How to extract data from TextBox through VBA

TextBox :

TextBox is used to display data, edit data, etc in the rectangular box. The Textbox can be linked to a Worksheet Cell. The TextBox can have static and dynamic value. When data or text is static in the TextBox field, it represents the read only information. It can be used on the WorkSheet or UserForm. You can see how it works and more details about ActiveX TextBox Control on the UserForm or Worksheet in the following chapter. 

Below is the few example to extract data from text box:

#1.

Sub textbox1()

Dim Textbox As String

Textbox = ActiveSheet.Shapes("Textbox 1").TextFrame.Characters.Text

‘’’’’’  Highlighted is Text box name’’’’’’’

MsgBox (Textbox)

End Sub

#2.

Sub textbox2()

Dim Textbox As String

Textbox = ActiveSheet.Shapes("Textbox 1").TextFrame.Characters.Text

ThisWorkbook.Worksheets("Main").Cells(15, 1).Value = Textbox

End Sub

Note: Please refer below link to extract data from once cell to multiple cells

Read Here

How To use MsgBox in VBA

MsgBox:

 The MsgBox function displays a message box and waits for the user to input and based of user input next operation will be performed.

Syntax:

MsgBox(prompt[,buttons][,title][,helpfile,context])

Below is the few examples of MsgBox :

#1.

 

Sub MsgBX()

''''' declaring variable '''''

Dim A AS STRING

Dim B AS STRING

'''''  Reading data from cell  '''''

A = ThisWorkbook.Worksheets("MsgBX").Cells(3, 2).Value

B = ThisWorkbook.Worksheets("MsgBX").Cells(4, 2).Value

 ''''' Displaying output  '''''

MsgBox (A & " " & B)

End Sub

 

 

#2.

Sub MsgBx1()

'''''' directly strings can be print using double quotes '''''

MsgBox ("Hello World")

End Sub

#3.

Sub MsgBx1()

'''''' directly strings can be print using double quotes '''''

MsgBox ( "Hello" & vbNewLine & "World")

End Sub

 

#4.

Sub MsgBx()

'''''' Below syntax Prompt,buttons,options '''''

A = MsgBox("Do you want to perform this operation", 3, "This is my Title")

'''''' 3 will give 3 buttons Yes, No & Cancel '''''

End Sub

 

Read More

How to connect to Cisco Information Server

Below is the code to connect Cisco Information Server and extract data:

Sub Cisco_connect()

 Dim Target_DB As String

 Dim Userid As String

 Dim Password As String

 Dim i As Integer

'''' Below Can be parameterized ‘‘‘‘

        Target_DB = "EMPINFO"

        Userid = "user"

        Password = "password"

                ''''' setting the required objects below' ''''

                Set connection = CreateObject("ADODB.Connection")

                connection.CommandTimeout = 3000

          ''' Cisco db connection string '''

                connection.Open "Driver=Cisco Information Server 7.0;DSN Name=" & Target_DB & ";Host=%HostName%; Port=%port%;uid=" & Userid & ";pwd=" & Password & ";Domain=%domain%;Datasource=%datasource%"

                

                Set Objrecordset = CreateObject("ADODB.Recordset")  

                StrQuery = " select * from EMPINFO.EMP  "

               '''' Recordset start''''

                Objrecordset.Open StrQuery, connection

                For i = 1 To Objrecordset.Fields.Count

                     ThisWorkbook.Worksheets("result").Cells(2, i).Value = Objrecordset.Fields(i - 1).Name

                Next

                     ThisWorkbook.Worksheets("result").Cells(3, 1).CopyFromRecordset Objrecordset

            ''''closing the connection ''''

                Objrecordset.Close

                connection.Close

 

           ThisWorkbook.Worksheets("result").Activate

           ThisWorkbook.Worksheets("result").Select

           ThisWorkbook.Worksheets("result").Range("A1").Select

     

    End Sub

   

 

 Read More: