Excecl VBA Classic Excel VBA 2010 Excel VBA 365 Excel VBA Examples About Us

Lesson 13 Do Loop


In this lesson, you will learn about another looping method know as the Do Loop in Excel VBA 365. There are four ways you can use the Do…Loop, as shown below:

(i) Do...Loop While
(ii) Do until...Loop
(iii) Do while...Loop
(iv) Do...Loop until

Example 13.1

Arranging numbers in ascending order

Private Sub CommandButton1_Click()
Dim counter As Integer
Do
counter = counter + 1
Cells(counter, 1) = counter
Loop While 
counter < 10
End Sub

Example 13.2

Private Sub CommandButton1_Click()
Dim counter , sum As Integer
'To set the alignment to center
 Range("A1:C11").Select
With Selection
 .HorizontalAlignment = xlCenter
End With

Cells(1, 1) = "X"
Cells(1, 2) = "Y"
Cells(1, 3) = "X+Y"

Do While counter <  10
 counter = counter + 1
 Cells(counter + 1, 1) = counter
 Cells(counter + 1, 2) = counter * 2
 sum = Cells(counter + 1, 1) + Cells(counter + 1, 2)
 Cells(counter + 1, 3) = sum
Loop
End Sub

In this example, the program will display the values of X in cells(1,1) to cells(11,1). The value of Y is X*2 and the values are displayed in column 2, i.e. from cells(2,1) to cells(2,11). Finally, it shows the values of X+Y in column 3, i.e. from cells(3,1) to cells(3,11)

Figure 13.1 The Output






Copyright ® 2020 Dr.Liew Voon Kiong . All rights reserved   [Privacy Policy]

Contact: Facebook Page