Looping is one of the most powerful concepts in Excel VBA 365 programming, allowing you to automate repetitive tasks efficiently. This lesson will teach you how to use different types of loops to process data, manipulate cells, and create dynamic solutions in your spreadsheets.
The For...Next loop is the most commonly used looping structure in VBA. It repeats a block of code a specific number of times, making it ideal for working with known quantities of data.
For counter = startNumber To endNumber [Step increment] ' Your VBA statements here Next [counter]
This example demonstrates how to fill cells in column A with sequential numbers and their squares in column B.
Private Sub CommandButton1_Click() Dim i As Integer For i = 1 To 10 Cells(i, 1).Value = i ' Column A gets numbers 1-10 Cells(i, 2).Value = i ^ 2 ' Column B gets squares Next i End Sub
This example shows how to use the Step keyword to control the increment value, creating a series of odd numbers.
Private Sub CommandButton1_Click() Dim i As Integer For i = 1 To 10 Step 2 ' Increment by 2 each iteration Cells(i, 1).Value = i ' Only odd numbers will be placed Next i End Sub
This additional example demonstrates counting from 10 down to 1 using a negative step value.
Private Sub CountDown() Dim i As Integer For i = 10 To 1 Step -1 ' Count down from 10 to 1 Cells(11 - i, 3).Value = i ' Place in column C Cells(11 - i, 4).Value = "Count: " & i ' Add text in column D Next i End Sub
Nested loops allow you to work with two-dimensional ranges, processing both rows and columns systematically.
For outerCounter = start To end For innerCounter = start To end ' Your VBA statements here Next innerCounter Next outerCounter
This example fills a 10x5 grid with the sum of each cell's row and column indices.
Private Sub CommandButton1_Click() Dim i As Integer, j As Integer For i = 1 To 10 ' Rows 1-10 For j = 1 To 5 ' Columns 1-5 (A-E) Cells(i, j).Value = i + j Next j Next i End Sub
This practical example creates a complete multiplication table using nested loops.
Private Sub CreateMultiplicationTable() Dim row As Integer, col As Integer ' Create headers For col = 1 To 10 Cells(1, col + 1).Value = col Cells(row + 1, 1).Value = row Next col ' Fill the table For row = 1 To 10 For col = 1 To 10 Cells(row + 1, col + 1).Value = row * col Next col Next row ' Format the table Range("B1:K11").Borders.Weight = xlThin Range("B1:K1").Font.Bold = True Range("A2:A11").Font.Bold = True End Sub
The While...Wend loop continues executing as long as a specified condition remains true. This is useful when you don't know in advance how many iterations will be needed.
While condition ' Your VBA statements here Wend
This example calculates squares of numbers until reaching the limit of 15.
Private Sub CommandButton1_Click() Dim i As Integer i = 1 While i <= 15 Cells(i, 1) = i ^ 2 i = i + 1 Wend End Sub
This practical example processes data in column A until it encounters an empty cell.
Private Sub ProcessDataUntilEmpty() Dim row As Integer row = 1 ' Process data until empty cell is found While Cells(row, 1).Value <> "" ' Double the value and place in column B Cells(row, 2).Value = Cells(row, 1).Value * 2 ' Highlight values over 100 If Cells(row, 1).Value > 100 Then Cells(row, 1).Interior.Color = RGB(255, 200, 200) End If row = row + 1 Wend MsgBox "Processed " & row - 1 & " rows of data.", vbInformation End Sub
row
, col
instead of just i
, j
In the next lesson, we'll explore the more flexible Do...Loop structures that give you greater control over your looping logic, including Do While, Do Until, and various exit conditions.