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

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