VBA Chapter 5-Looping

Looping is a very useful feature of Visual Basic because it makes repetitive works easier. There are two kinds of loops in Excel VBA, the For.......Next loop and the Do...Loop . To demonstrate the For....Next loop in Excel VBA, here are two examples.

Example 1:

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

In this VBA program, you place the command button 1 on the spreadsheet then click on it to go into the Visual Basic editor. When you click on the button , the 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 row and j represent column.

In example 2,we use the nested loop to put 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 and output are shown below.

Example 1

Example 2

Codes of Example 2

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 j
Next i
End Sub
　

Chapter 5: Looping