Loops

Module 4 of 9

REMINDER: To fully understand Loops, you should have already watched the Loops Video in Canvas and followed along with the Macro Demo file. This practice will build upon that foundation.

How Loops Work in VBA

When a macro runs, it executes each line once, top to bottom. That works fine for a handful of actions — but if you need to process 30 student records, you don't want to write the same logic 30 times. Loops let you write the code once and repeat it automatically for every record in your data.

VBA has three loop structures: For Next, Do While, and Do Until. They all repeat a block of code but differ in how they decide when to stop. For this course, Do Until is the one you'll use most — it's built for processing rows of data when you don't know in advance how many records there are.

The "move to next record" line at the bottom of the loop is the one students forget most often. If you forget it, the loop processes the same row forever. Write that line before you write anything else inside the loop.

For Next Loop

Use a For Next loop when you know exactly how many times the loop needs to run. It uses a counter variable that starts at a value you set, runs the code inside, increments by 1, then checks if it's reached the end value. If not, it loops again. You can use any variable name for the counter — i is conventional — and you can use variables for the start and end values instead of hardcoded numbers.

Syntax

For [counter] = [start] To [end]
    ' your code here
Next [counter]

This example puts the numbers 1 through 4 into adjacent columns:

For i = 1 To 4
    ActiveCell.Value = i
    ActiveCell.Offset(0, i).Select
Next i

Do While Loop

A Do While loop runs as long as a condition is true. VBA checks the condition before each pass — if it's true, the code inside runs; if it's false, the loop stops. If the condition is already false when the loop starts, the code inside never runs at all. The condition must involve a variable that changes inside the loop, otherwise the condition never becomes false and the loop runs forever.

Syntax

Do While [condition is true]
    ' your code here
Loop

This example counts how many times it subtracts 1 from a number before reaching 10:

myNum = 20

Do While myNum > 10
    myNum = myNum - 1
    counter = counter + 1
Loop

MsgBox "The loop made " & counter & " repetitions."

Do Until Loop

A Do Until loop runs until a condition becomes true — which means it keeps going while the condition is false. For processing rows of data, the condition is always a blank cell check: Do Until ActiveCell = "". The loop continues as long as there's data in the current cell and stops when it hits a blank row. This works for 10 records or 10,000 without changing a single line of code.

Syntax

Do Until [condition is true]
    ' your code here
Loop

The same counting example using Do Until — notice it reads as the inverse of Do While:

myNum = 20

Do Until myNum = 10
    myNum = myNum - 1
    counter = counter + 1
Loop

MsgBox "The loop made " & counter & " repetitions."

Processing All Records

In this course, loops are almost always used to process rows of data — going through a list of students or applicants and acting on each one. This pattern is the same every time: navigate to the first record, loop until you hit a blank cell, do your work, then move down one row at the very end of the loop body.

Syntax

SELECT first record (e.g. Range("A2").Select)
DO UNTIL ActiveCell = ""
    ' process current row
    MOVE to next record (ActiveCell.Offset(1, 0).Select)
ENDLOOP

Here is what this pattern looks like in the actual Project Demo — the loop that processes student applicants:

Range("A2").Select

Do Until ActiveCell = ""

    If ActiveCell.Offset(0, 7) = Range("AcceptValue") Then
        ' process this record
        NumberAccepted = NumberAccepted + 1
    End If

    ' Always move to next row — inside or outside the IF
    ActiveCell.Offset(1, 0).Select

Loop

Primary key column: When using Do Until ActiveCell = "", you must be in a column where every record has a value. A primary key column (like UIN or StudentID) is the safest choice — if any cell in your loop column is blank, the loop will exit early and miss records.

Cautions: Endless Loops

An endless loop happens when the condition never becomes true. The three common causes: the condition doesn't involve a variable, the variable never changes inside the loop, or the "move to next record" line is inside an IF block instead of outside it.

  • The condition MUST involve at least one variable
  • That variable MUST change value somewhere inside the loop body
  • The "move to next record" line must be OUTSIDE any IF block — it should always execute, whether the IF condition is true or false. Placing it INSIDE the IF block causes an endless loop whenever the condition is false.

Here is what a loop looks like when the move line is missing:

Range("A2").Select

Do Until ActiveCell = ""

    UIN = ActiveCell

    ' MISSING: MOVE to next record — without this, endless loop

Loop

⚠ Cautions: Relying on AI

AI-generated loop code often looks correct but skips the iteration step or uses the wrong exit condition. Always trace the loop by hand or step through with F8 before trusting it.

Sanders-style code makes the iteration explicit; AI patterns often hide it inside a function call.

Ctrl+Break / saving: If you get stuck in an endless loop, press Ctrl+Break to stop it. Ctrl+Break does not always work — you may need to force-quit Excel. Always save your file before running a macro so you don't lose your code if Excel has to be restarted.

Simple Do Until — mirrors how you navigate in Excel

Range("A2").Select

Do Until ActiveCell = ""
    ' process current row
    ActiveCell.Offset(1, 0).Select
Loop

For Each with object variables

' AI approach — uses concepts this course doesn't cover
Dim ws As Worksheet
Dim lastRow As Long
Dim cell As Range

Set ws = ThisWorkbook.Sheets("Student Information")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

For Each cell In ws.Range("A2:A" & lastRow)
    If cell.Value <> "" Then
        ' process row
    End If
Next cell

Both loops process every record — but they don't look the same. The Sanders approach mirrors how you navigate in Excel: start at A2, loop until blank, move down one row. You can watch it run with F8 and see exactly where the cursor is at every step.

The AI version works, but it uses concepts this course doesn't cover — object variables, For Each, finding the last row with End(xlUp). If you use code like this without understanding it, debugging with F8 will be confusing and the exam won't go well.

Quick Check

1. You have a macro that needs to process every student record in a spreadsheet. You don't know how many students there are. Which loop should you use?

2. In a Do Until loop, the code inside the loop runs when the condition is _____.

3. What happens if you forget the ActiveCell.Offset(1, 0).Select line at the bottom of your Do Until loop?

4. Which of the following is the correct pattern for processing all records in an Excel table using a Do Until loop?

5. In a For Next loop with For i = 1 To 4, how many times does the loop run?

Question 2 trips people up every semester. Do Until sounds like it runs until something is true — but the code inside runs while the condition is false. Draw it out: the loop asks "am I done yet?" and keeps going as long as the answer is no.

Easy Wins

Write Your First For Next Loop Guided

Write a macro that uses a For Next loop to put the numbers 1 through 5 into cells A1 through A5, one number per cell.

Press Alt+F11 to open the VBA Editor. Go to Insert → Module. You should see a blank white area. This is where you write your code.

Type this shell to start:

Option Explicit
Sub NumberCells()

End Sub

Option Explicit forces you to declare variables. Always include it.

Test the Do Until Condition Observation

This is a quick experiment, not a build exercise.

Go back to the ChkFirstUntil example from the concept section above. Change myNum = 20 to myNum = 9. Step through the code with F8 and watch what happens as each line executes — press Ctrl+Break when you've seen enough.

What do you notice? Why?

Count Non-Blank Cells with Do Until Guided

Write a macro that starts at cell A1, uses a Do Until loop to move down the column, and counts every non-blank cell it finds. Display the count in a MsgBox when done.

Open the VBA Editor (Alt+F11). Add a new module (Insert → Module) or use the same one. Create this shell:

Option Explicit
Sub CountCells()

End Sub

Sample Data

Copy this data and paste it into cell A1 of a new Excel worksheet. Use Ctrl+V to paste — Excel will automatically split the columns. This is your practice dataset for the exercises below.

Practice Problem

Count Accepted Applicants

Using the Aggie Advisors data you just pasted into Excel, write a macro that loops through all 30 applicant records and counts only the students whose FinalDecision column says "Accept". Display the result in a MsgBox.

What your macro needs to do:

  • Start at cell A2 (row 1 is the header)
  • Use Do Until ActiveCell = "" to loop through all records
  • Inside the loop, check if the FinalDecision column = "Accept" (FinalDecision is in column H — that's Offset(0, 7) from column A)
  • Add 1 to a counter variable each time you find an "Accept"
  • Move to the next row at the bottom of the loop — every time, whether the decision was Accept or not
  • After the loop, display: "Accepted applicants: " & your counter

Expected answer: Accepted applicants: 20

Hint: Your IF statement should look like this:

If ActiveCell.Offset(0, 7) = "Accept" Then
    AcceptCount = AcceptCount + 1
End If

The move line goes AFTER the End If, not inside it.

Challenge

These exercises are perfect preparation for the Sample Exam, which prepares you for the Macro Project Exam itself. Exam format may vary by semester. Note that the exam questions will be more comprehensive than this single Challenge; this is just the first step to help build your foundation.

Accepted and Denied GPR Averages

No hints. No steps. This is exam level.

Using the same Aggie Advisors dataset, write a macro that:

  1. Loops through all 30 applicant records using Do Until with blank cell check
  2. Separately accumulates total GPR for accepted students and denied students
  3. Counts accepted and denied students separately
  4. After the loop, calculates the average GPR for each group
  5. Displays both averages in a single MsgBox: "Accepted avg GPR: X.XX | Denied avg GPR: X.XX"

Must use Option Explicit. Must declare all variables with correct data types. Must use a Named Range or the literal string "Accept" consistently — do not mix approaches.

Expected answers:

  • Accepted: 20 students, average GPR 3.7175
  • Denied: 10 students, average GPR 3.0014

If your numbers don't match, use F8 and the Watch Window to step through the first few records and check what your IF condition is actually comparing.