Excel Easy 1: LOOP

Sunday, April 14, 2019

CÁCH DÙNG VÒNG LẬP FOR … NEXT LOOP

– Vòng lập rất hay được sử dụng trong VBA hay trong các ngôn ngữ khác vì chúng ta có thể lấy tất cả các dữ liệu thông qua nó.

– Có 2 loại vòng lập: For … Next loop và Do … Loop

– Trong bài này chúng ta có tìm hiểu kỹ vòng lập For … Next Loop

For counter=startNumber to endNumber (Step increment)

One or more VB statements

Next

Ví dụ 1

Dim i As Integer

For i = 0 To 10 Step 1

Cells(i + 1, 1) = i

Ví dụ 2

Dim i As Integer

For i = 1 To 15 Step 2

Cells(i, 1).Value = i

Ví dụ 3

Dim i As Integer

For i = 1 To 15

Cells(i, 1) = i

If i >= 10 Then

Exit For

End If

Ví dụ 4

Dim i, j As Integer

For i = 1 To 10

For j = 1 To 5

Cells (i, j) = i + j

Next j

Next i

Ví dụ 5

Dim i, counter As Integer

For i = 1 To 20

If Cells(i, 2) > 50 Then

counter = counter + 1

Cells(i, 2).Font.ColorIndex = 5

Else

‘do nothing

Cells(i, 2).Font.ColorIndex = 3

End If

Cells(21, 2) = counter

Cells(22, 2) = 20 – counter

CÁCH DÙNG DO……LOOP

Có 4 loại vòng lập DO … Loop
a) Do While condition
Block of one or more VB statements
Loop
b) Do
Block of one or more VB statements
Loop While condition
c) Do Until condition
Block of one or more VB statements
Loop
d) Do
Block of one or more VB statements
Loop Until condition

Ví dụ

Ví dụ 1
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
Ví dụ 2
Dim counter As Integer
Do
counter = counter + 1
Cells(counter, 1) = counter
Loop While counter < 10
Ví dụ 3

Dim counter As Integer
Do Until counter = 10
counter = counter + 1
Cells(counter, 1) = 11 – counter
Loop
Ví dụ 4
Dim counter As Integer
Do
counter = counter + 1
Cells(counter, 1) = 11 – counter
Loop Until counter = 10