MS-Excel / General Formatting

---

Using Do..Loop Structures

What do you do when you need to loop but you don't know in advance how many times to repeat the loop? This could happen if, for example, you want to loop only until a certain condition is met, such as encountering a blank cell in an Excel worksheet. The solution is to use a Do...Loop.

The Do...Loop has four different syntaxes:

Do While condition [statements] Loop
Checks condition before entering the loop. Executes the statements only while condition is True.

Do [statements] Loop While condition
Checks condition after running through the loop once. Executes the statements only while condition is True. Use this form when you want the loop to be processed at least once.

Do While Until condition [statements] Loop
Checks condition before entering the loop. Executes the statements only while condition is False.

Do [statements] Loop Until condition
Checks condition after running through the loop once. Executes the statements only while condition is False. Again, use this form when you want the loop to be processed at least once.

Listing shows a procedure called BigNumbers that runs down a worksheet column and changes the font color to magenta whenever a cell contains a number greater than or equal to 1,000.

A Procedure That Uses a Do...Loop to Process Cells Until It Encounters a Blank Cell

Sub BigNumbers()
    Dim rowNum As Integer, colNum As Integer, currCell As Range
    '
    ' Initialize the row and column numbers
    '
    rowNum = ActiveCell.Row
    colNum = ActiveCell.Column
    '
    ' Get the first cell
    '
    Set currCell = ActiveSheet.Cells(rowNum, colNum)
    '
    ' Loop while the current cell isn't empty
    '
    Do While currCell.Value <> ""
        '
        ' Is it a number?
        '
        If IsNumeric(currCell.Value) Then
            '
            ' Is it a big number?
            '
            If currCell.Value >= 1000 Then
                '
                ' If so, color it magenta
                '
                currCell.Font.Color = VBAColor("magenta")
            End If
        End If
        '
        ' Increment the row number and get the next cell
        '
        rowNum = rowNum + 1
        Set currCell = ActiveSheet.Cells(rowNum, colNum)
    Loop
End Sub

The idea is to loop until the procedure encounters a blank cell. This is controlled by the following Do While statement:

Do While currCell.Value <> ""

currCell is an object variable that is set using the Cells method. Next, the first If...Then uses the IsNumeric function to check whether the cell contains a number, and the second If...Then checks whether the number is greater than or equal to 1,000. If both conditions are True, the font color is set to magenta by the VBAColor function described earlier in this tutorial.