How to Use the Offset Property to Increment Objects in Excel VBA

In this article, we will learn how to increment the object in a range of a for each loop in Excel VBA. A for each loop is a type of loop that iterates through a collection of objects, such as a range of cells, and performs a certain action on each object. An object is a variable that represents an element of the collection, such as a cell or a worksheet. To increment the object means to change its value or position by a certain amount, such as adding one or moving to the next column.

To increment the object in a range of a for each loop, we need to use the Offset property of the Range object. The Offset property returns a new range that is offset from the original range by a specified number of rows and columns. For example, Range("A1").Offset(1, 1) returns the range B2, which is one row down and one column to the right of A1. We can use the Offset property to change the value or position of the object in the loop by assigning a new value to the offset range or setting the object to the offset range.

Procedures

To increment the object in a range of a for each loop, we need to follow these steps:

1. Declare and initialize the variables that we will use in the loop, such as the range, the object, and the increment amount.
2. Use the For Each…Next statement to create the loop that iterates through the range of objects.
3. Inside the loop, use the Offset property to increment the object by the specified amount. We can either assign a new value to the offset range or set the object to the offset range, depending on what we want to achieve.
4. End the loop with the Next statement.

Example

To illustrate how to increment the object in a range of a for each loop, let us consider the following scenario:

• We have a worksheet named “Sales” that contains the sales data of a company for the year 2023.
• The data is organized in a table that starts from cell A1 and has the following columns: Month, Product, Quantity, Price, and Revenue.
• We want to create a macro that loops through the table and increases the price of each product by 10% and recalculates the revenue accordingly.

Here is the code that we can use to achieve this:

Sub Increment_Price()
    'Declare and initialize the variables
    Dim rng As Range 'The range of the table
    Dim obj As Range 'The object in the loop
    Dim inc As Double 'The increment amount
    Set rng = Worksheets("Sales").Range("A1").CurrentRegion 'Set the range to the table
    inc = 0.1 'Set the increment amount to 10%
    
    'Loop through the range of objects
    For Each obj In rng.Columns(4).Cells 'Loop through the cells in the fourth column (Price)
        If obj.Row > 1 Then 'Skip the header row
            obj.Offset(0, 0).Value = obj.Offset(0, 0).Value * (1 + inc) 'Increase the price by 10%
            obj.Offset(0, 1).Value = obj.Offset(0, -1).Value * obj.Offset(0, 0).Value 'Recalculate the revenue
        End If
    Next obj 'End the loop
End Sub

Here is the table before and after running the macro: