Tag: Do until…Loop

 [Lesson 11]<<[Table of Contents]>>[Lesson 13]

Excel 2010  VBA  allows a procedure to repeat many times until a condition is met. This is called looping.  There are three kinds of loops,  the For…….Next loop, the While….Wend loop and the Do…Loop .

12.1 One level For…..Next Loop

The one level For….Next Loop event procedure is written as follows:

For counter=startNumber to endNumber (Step increment)
 One or more VB statements
Next

*We shall deal with the For………Next loop and the While….Wend loop first and the Do….Loop in the next lesson.

Example 12.1

In this Excel 2010 VBA  program, you place the command button on the spreadsheet then click on it to go into the Excel 2010 VBA editor. When you click on the button, the Excel VBA program will fill cells(1,1) with the value of 1, cells(2,1) with the value of 2, cells(3,1) with the value of 3……until cells (10,1) with the value of 10. The position of each cell in the Excel spreadsheet is referenced with cells(i,j), where i represents a row and j represent a column. We shall learn more about cells in future lessons.

The Code

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 10
 Cells(i, 1).Value = i
 Cells(i,2).Value=i^2
Next
End Sub

* i^2 means i²

The Output is shown in Figure 12.1

Figure 12.1

Example 12.2

For……..Next loop with step increments. In this example, the number increases by 2 at a time. The resulting series is 1,3,5,7,9 displayed in Cells(1,1), Cells(3,1),Cells(5,1), Cells(7,1) and Cells(9,1) respectively.

The code

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 10  Step 2
 Cells(i, 1).Value = i
Next
End Sub

The Output

Figure 12.2

12.2 Nested For….Next Loops

Nested For…….Next loops mean there more than one For…Next Loops are nested within the first level For…..Next loop. The structure is as follows:

For counter=startNumber1 to endNumber1
For counter=startNumber2 to endNumber2
For counter=startNumber3 to endNumber3
 One or more VB statements
Next
Next
Next

Example 12.3

In this example , we use  nested loop to insert the values of i+j from cells(1,1),cells(1,2),cells(1,3),cells(1,4),cells(1,5) ……….until cells(10,5).

The code 

Private Sub CommandButton1_Click()
Dim i, j As Integer
For i = 1 To 10
For j = 1 To 5
 Cells(i, j).Value = i + j
Next
Next
End Sub

The Output

Figure 12.3

12.3  While….Wend  Loop

The structure of a While….Wend Loop is very similar to the Do Loop. it takes the following form:

While condition
 Statements
Wend

The above loop means that while the condition is not met, the loop will go on. The loop will end when the condition is met. Let’s examine the program listed in example 12.4.

Example 12.4

In this example, the Excel VBA program will compute the square of numbers from 1 to 15 and displays them from Cells(1,1) to Cells(15,1)

The Code

Private Sub CommandButton1_Click()
Dim i As Integer
i = 1
While i <= 15
 Cells(i, 1) = i ^ 2
 i = i + 1
Wend
End Sub

The Output

Figure 12.4

 [Lesson 11]<<[Table of Contents]>>[Lesson 13]