How to loop through a range of cells in Excel VBA

The basic theory behind running a code on a range of cells and looping back infinitely in Excel is to use a loop structure that iterates over each cell in the range and performs some action on it. There are different ways to loop through a range of cells in Excel, such as using the For…Next loop, the For Each…Next loop, or the Do…Loop statement. Each of these methods has its own syntax and advantages, depending on the situation.

The general procedure for looping through a range of cells is as follows:

• Define the range of cells that you want to loop through. You can use the Range property, the Cells property, the CurrentRegion property, or any other way to specify a range of cells in Excel.
• Use a loop structure to iterate over each cell in the range. You can use the For…Next loop, the For Each…Next loop, or the Do…Loop statement. You need to specify the starting and ending points of the loop, and optionally the increment or decrement of the loop counter.
• Inside the loop, perform the action that you want to do on each cell. You can use the Value property, the Formula property, the Interior property, or any other property or method of the Range object to manipulate the cell.
• Exit the loop when the condition is met or when you want to stop the loop. You can use the Exit For or Exit Do statement to exit the loop prematurely, or you can use a variable or a cell value to control the loop condition.

Here is a comprehensive explanation of how to loop through a range of cells using the For Each…Next loop, which is one of the easiest and most common ways to do it.

• The For Each…Next loop allows you to loop through each element in a collection or an array. In Excel, you can use the For Each…Next loop to loop through each cell in a range of cells. The syntax of the For Each…Next loop is:

For Each element In collection
    [statements]
Next [element]

• The element variable is a placeholder for each element in the collection. You can use any name for the element variable, but it is a good practice to use a descriptive name that indicates what the element is. For example, you can use c, cell, or rng for a cell in a range.
• The collection is the name of the collection or the array that you want to loop through. In Excel, you can use the Range property or the Cells property to specify a collection of cells. For example, you can use Range(“A1:D10”) or Cells(1, 1).Resize(10, 4) to refer to the same range of cells from A1 to D10.
• The statements are the code that you want to execute on each element in the collection. You can use the element variable to refer to the current element in the loop. For example, you can use c.Value to get or set the value of the current cell in the loop.
• The Next statement marks the end of the loop. You can optionally include the element variable after the Next statement to improve the readability of the code. For example, you can use Next c or Next cell to indicate that the loop is over for the current cell.

Here is an example of how to use the For Each…Next loop to loop through the range A1:D10 and set the value of each cell to 0 if the absolute value of the cell is less than 0.01.

Sub RoundToZero()
    'Declare a variable to hold the current cell in the loop
    Dim c As Range
    'Loop through each cell in the range A1:D10
    For Each c In Range("A1:D10")
        'Check if the absolute value of the cell is less than 0.01
        If Abs(c.Value) < 0.01 Then
            'Set the value of the cell to 0
            c.Value = 0
        End If
    'Move to the next cell in the loop
    Next c
End Sub

To loop back infinitely, you need to use a loop structure that does not have a fixed ending point, such as the Do…Loop statement. The Do…Loop statement allows you to repeat a block of code while a condition is true or until a condition becomes true. The syntax of the Do…Loop statement is:

Do [{While | Until} condition]
    [statements]
[Exit Do]
    [statements]
Loop

• The condition is a logical expression that evaluates to True or False. You can use the While keyword or the Until keyword to specify when the loop should end. If you use the While keyword, the loop will continue as long as the condition is true. If you use the Until keyword, the loop will continue until the condition becomes true.
• The statements are the code that you want to repeat in the loop. You can use the Exit Do statement to exit the loop prematurely, based on some criteria. For example, you can use a variable or a cell value to control the loop condition.
• The Loop statement marks the end of the loop.

Here is an example of how to use the Do…Loop statement to loop through the range A1:D10 and set the value of each cell to 0 if the absolute value of the cell is less than 0.01, and then loop back to the first cell and repeat the process infinitely.

Sub RoundToZeroInfinite()
    'Declare a variable to hold the current cell in the loop
    Dim c As Range
    'Start an infinite loop
    Do
        'Loop through each cell in the range A1:D10
        For Each c In Range("A1:D10")
            'Check if the absolute value of the cell is less than 0.01
            If Abs(c.Value) < 0.01 Then
                'Set the value of the cell to 0
                c.Value = 0
            End If
        'Move to the next cell in the loop
        Next c
        'Check if the user wants to stop the loop
        If Range("E1").Value = "STOP" Then
            'Exit the infinite loop
            Exit Do
        End If
    'Continue the infinite loop
    Loop
End Sub

In this example, the user can enter the word “STOP” in cell E1 to stop the infinite loop. Otherwise, the loop will keep running and updating the values in the range A1:D10.

To illustrate the result of the code, let us assume that the range A1:D10 has the following values before running the code:

After running the code, the range A1:D10 will have the following values:

The code will keep looping through the range and setting the values to 0 if they are less than 0.01 in absolute value, until the user enters “STOP” in cell E1.

Other approaches to loop through a range of cells and loop back infinitely are to use the For…Next loop or the Do While…Loop statement. The For…Next loop allows you to loop through a range of cells by specifying the starting and ending points and the increment or decrement of the loop counter. The syntax of the For…Next loop is:

For counter = start To end [Step increment]
    [statements]
[Exit For]
    [statements]
Next [counter]

• The counter is a variable that is used to count the number of times the loop is executed. You can use any name for the counter variable, but it is a good practice to use a descriptive name that indicates what the counter is. For example, you can use i, j, or k for a loop counter.

• The start and end are the values that define the range of the loop counter. You can use any numeric expression or variable to specify the start and end values. For example, you can use 1, 10, or n to define the start and end values.
• The increment is the amount by which the loop counter is increased or decreased each time the loop is executed. You can use any numeric expression or variable to specify the increment value. The increment value can be positive or negative, depending on the direction of the loop. If you omit the increment value, it is assumed to be 1 by default. For example, you can use 1, -1, or m to define the increment value.
• The statements are the code that you want to execute in each iteration of the loop. You can use the counter variable to refer to the current value of the loop counter. For example, you can use i.Value to get or set the value of the cell in the ith row and the first column.
• The Exit For statement allows you to exit the loop prematurely, based on some criteria. For example, you can use a variable or a cell value to control the loop condition.
• The Next statement marks the end of the loop. You can optionally include the counter variable after the Next statement to improve the readability of the code. For example, you can use Next i or Next j to indicate that the loop is over for the current value of the loop counter.

Here is an example of how to use the For…Next loop to loop through the range A1:D10 and set the value of each cell to 0 if the absolute value of the cell is less than 0.01, and then loop back to the first cell and repeat the process infinitely.

Sub RoundToZeroInfinite2()
    'Declare a variable to hold the current row in the loop
    Dim i As Long
    'Declare a variable to hold the current column in the loop
    Dim j As Long
    'Start an infinite loop
    Do
        'Loop through each row in the range A1:D10
        For i = 1 To 10
            'Loop through each column in the range A1:D10
            For j = 1 To 4
                'Check if the absolute value of the cell is less than 0.01
                If Abs(Cells(i, j).Value) < 0.01 Then
                    'Set the value of the cell to 0
                    Cells(i, j).Value = 0
                End If
            'Move to the next column in the loop
            Next j
        'Move to the next row in the loop
        Next i
        'Check if the user wants to stop the loop
        If Range("E1").Value = "STOP" Then
            'Exit the infinite loop
            Exit Do
        End If
    'Continue the infinite loop
    Loop
End Sub

In this example, the user can enter the word “STOP” in cell E1 to stop the infinite loop. Otherwise, the loop will keep running and updating the values in the range A1:D10.

The Do While…Loop statement is similar to the Do…Loop statement, except that it checks the condition at the beginning of the loop instead of at the end. The syntax of the Do While…Loop statement is:

Do While condition
    [statements]
[Exit Do]
    [statements]
Loop

• The condition is a logical expression that evaluates to True or False. The loop will continue as long as the condition is true. If the condition is false at the start of the loop, the loop will not run at all.
• The statements are the code that you want to repeat in the loop. You can use the Exit Do statement to exit the loop prematurely, based on some criteria. For example, you can use a variable or a cell value to control the loop condition.
• The Loop statement marks the end of the loop.

Here is an example of how to use the Do While…Loop statement to loop through the range A1:D10 and set the value of each cell to 0 if the absolute value of the cell is less than 0.01, and then loop back to the first cell and repeat the process infinitely.

Sub RoundToZeroInfinite3()
    'Declare a variable to hold the current cell in the loop
    Dim c As Range
    'Start an infinite loop
    Do While True
        'Loop through each cell in the range A1:D10
        For Each c In Range("A1:D10")
            'Check if the absolute value of the cell is less than 0.01
            If Abs(c.Value) < 0.01 Then
                'Set the value of the cell to 0
                c.Value = 0
            End If
        'Move to the next cell in the loop
        Next c
        'Check if the user wants to stop the loop
        If Range("E1").Value = "STOP" Then
            'Exit the infinite loop
            Exit Do
        End If
    'Continue the infinite loop
    Loop
End Sub

In this example, the user can enter the word “STOP” in cell E1 to stop the infinite loop. Otherwise, the loop will keep running and updating the values in the range A1:D10.