Logo
×

Looping in VBA

Looping in VBA

There are many situations where a programmer needs to execute a block of the repetitive code number of times. Writing the same statement will make the program tedious and monotonous. Hence, to reduce the statements, looping is introduced. A loopin Excel VBA enables you to loop through a range of cells with just a few code lines. Thus, it allows the programmer to execute a statement or group of statements multiple times.

 Loops are used to repeat a block of the statement as many times as required until a given condition remains true or a specific point (or value) is reached, after which the next section of code is executed. A loop allows the programmer to write a few lines of code and accomplish an output (just by repetition or looping).

The various loops in VBA are as follows:

  1. for loop

The for loop is used to execute a sequence of statements multiple times and shortens the code to manage the loop variable.

  • for…each loop

The for…each loop is used to execute if there is at least one element in the group and reiterated for each element in a group.

  • while…wend loop

This loop tests the condition before executing the loop body.

  • do..while loops

The do..While will keep on looping or executing the block of code  till the specified condition is true.

  • do..until loops

This loop is used to repeat a set of statements if the condition is false. The condition may be checked at the beginning of the loop or at the end of Do Until loop.

Example 1: Write a procedure or macro using Do Loop that does the following:

            Get "Qualified" in column E if the value of column D is above 200 

Sub DoWhile_Loop_Example1()
 'initializing the variable r
 Dim r As Byte
 'Setting up a value of variable 'r'
 r = 2
     'Do While will loop until the cells' value is not equal to empty
     Do While Cells(r, 1) <> ""
         'IF Condition Block
         If Cells(r, 4) > 200 Then
             Cells(r, 5) = "Qualified" 
         Else
             Cells(r, 5) = "Disqualified"
         End If
         'Adding one to r counter to move to the next cell
         r = r + 1
     'Closing the while loop
     Loop
 End Sub

Output

Name Title Date Sale Amount Qualified/Disqualified
Leo Hendry Customer Service 6/8/2016 $220.00 Qualified
Rani Bhardwaj Sales 1/1/2014 $251.00 Qualified
Elmer C. Laslo Customer Service 6/16/2016 $217.00 Qualified
Ayush Khurana Data Entry Specialist 5/31/2015 $344.00 Qualified
Gerard M. Banmiller Customer Service 1/11/2014 $980.00 Qualified
Gregory L. Gibson IT Manager 11/22/2015 $178.00 Disqualified
Megha Malik Customer Service 3/31/2014 $258.00 Qualified
N. William White Sales Manager 12/21/2015 $38.00 Disqualified
Robert F. Mangano Data Entry Specialist 7/16/2015 $371.00 Qualified
Itrat Zaidi IT Tech 6/8/2016 $67.00 Disqualified
Thomas E. Vessey Business Development Executive 3/21/2016 $437.00 Qualified
Looping in VBA

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 
             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
Looping in VBA2

Related Topics

Excel VBA INT Function

VBA INT Function: The INT function in VBA rounds the given supplied number down and returns an integer value. The positive numbers are rounded to zero, and the negative numbers are rounded away from zero. Syntax Int...

1 minute read.

Excel VBA CDbl Function

VBA CDbl Function: The CDbl function in VBA converts an expression into a Double data type. Syntax CDbl (Expression) Parameter Expression (required) - This parameter represents the expression that that you want to convert to...

1 minute read.

Excel VBA InStr Function

VBA InStr Function The InStr function in VBA searches for a substring inside the given string. It returns the position of a substring within a string, as an integer if the substring is found...

1 minute 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 Exp Function

VBA Exp Function: The Exp function in VBA returns the value of the exponential function ex (mathematical constant ‘e’ raised to specified power) for the given value of x. Syntax Exp (Number) Parameter Number (required) –This parameter represents...

1 minute read.

Excel VBA Date Function

VBA Date Function The Date function in VBA returns the current date. Syntax Date( ) Parameter NA Return This function returns the current date. Example 1 Sub DateFunction_Example1() ' retuning the current date in the variable currentDate Dim currentDate As...

1 minute read.

VBA Subscript out of Range

What is Subscript out of Range? The VBA Subscript out of Range error (which is also called as Run-Time Error 9) mostly triggers when the user selects any cell, sheet, or workbook which does...

5 minutes read.

VBA Find Function

VBA Find Function The Excel VBA FIND function finds any information in your Excel. It can be used on a Range object on the worksheet. It works the same, unlike the Excel Find &...

6 minutes read.

Excel VBA AutoFilter

One of the reasons for Excel VBA’s popularity is its capability to filter and analyze data from huge database with the help of a method known as AutoFilter. This method permits a...

4 minutes read.

Excel VBA: Do Until….Loop

DO UNTIL….Loop The “Do Until” Loop is same unlike DO WHILE statement just that it will keep on looping till the condition is not met. This loop is used to repeat a set...

4 minutes read.

Excel VBA CSng Function

VBA CSng Function: The CSng function in VBA converts the supplied expression into a single-precision floating-point number (single data type). Syntax CSng (Expression) Parameter Expression (required) – This parameter represents the expression that you want...

1 minute read.

Looping in VBA

Looping in VBA There are many situations where a programmer needs to execute a block of the repetitive code number of times. Writing the same statement will make the program tedious and monotonous....

3 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 LTrim Function

VBA LTrim Function: The LTrim function in VBA removes the leading spaces from a supplied text string. Syntax LTrim (String) Parameter String (required) - This parameter represents he text string that you want to remove...

1 minute read.

Excel VBA Tan Function

VBA Tan Function: The Tan function in VBA returns the tangent for the specified angle in radians. Syntax Tan (Number) Parameter Number (required) – This parameter represents the angle supplied in radiant that you want...

1 minute read.

Excel VBA InStrRev Function

VBA InStrRev Function: The InStrRev function in Excel VBA returns an integer representing the position of a substring within the specified string if the substring is fount else it returns...

2 minutes read.

VBA VBScript Regex Methods Regex

VBA VBScript Regex Methods Regex The VBA Regex supports 3 methods which are as follows: ExecuteReplaceText Execute The execute method is used to extract a match from the given based on the defined matching...

3 minutes read.

Excel VBA Sqr Function

VBA Sqr Function: The Sqr function in VBA returns the square root for the specified number. Syntax Sqr (Number) Parameter Number (required) – This parameter represents a positive numeric value that you want to calculate...

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.

Excel VBA DateSerial Function

The DateSerial function in VBA returns a Date from a supplied year, month, and day number. Syntax DateSerial (Year, Month, Day) Parameter Year (required) – This parameter represents an integer signifying the year. Month (required) – This parameter...

2 minutes read.