# Excel VBA Offset Within Loop (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will talk about the usage of Excel VBA Offset Within Loop. It is a very effective function in its own right. However, when combined with loops, it serves multiple purposes, from selecting cells to querying with criteria. This article will help users understand the usage of the Offset function in a VBA loop.

The Offset function inside a while loop in the overview video calculates the revenue of each fruit.

## How to Launch VBA Macro Editor in Excel

In order to run any VBA code, we first need to write or edit the code in the VBA Macro Editor.

Before that, we need to enable the Developer tab in our Excel sheet. The Developer tab allows users to access the VBA Macro Editor.

Follow the simple steps below to open up the VBA Macro Editor. • Go to Developer Tab >> Visual Basic.
• This will open the Visual Basic window. • Select Insert >> Module in the macro editor. • As a result, an empty module will appear on the screen where you can write the code.

## Excel VBA Offset Within Loop: 5 Suitable Examples

Today, we are going to discuss 4 examples of the Offset function in VBA Loops. Here, we gave a data set of a fruit business with sales numbers for different months and unit prices. We will use the Offset function in different types of loops: While Loop, For Next Loop, and Do While Loop to find the revenue and maximum sales for each fruit as well as the overall highest sales among all fruits.

### 1. Using Offset Function Inside While Loop to Calculate Revenue

The following code uses the Offset function inside a While Loop and calculates the revenue of each fruit in the dataset. ``````Sub Offset_While()
'declaring range variable
Dim Rng As Range
'setting value to the variable
Set Rng = Range("B5")
'running while loop to calculate revenue
While Rng.Value <> ""
Rng.Offset(0, 6).Value = Rng.Offset(0, 1).Value * Rng.Offset(0, 5).Value
Set Rng = Rng.Offset(1, 0)
Wend
End Sub``````

VBA Breakdown:

``````Dim Rng As Range
Set Rng = Range("B5")
While Rng.Value <> ""
Rng.Offset(0, 6).Value = Rng.Offset(0, 1).Value * Rng.Offset(0, 5).Value
Set Rng = Rng.Offset(1, 0)
Wend``````

At first, the code creates a variable of type Range called Rng and assigns the value Range(“B5”). It implies that the range will begin at that cell.

The While Loop first determines whether or not the value of the cell that the  Rng variable refers to is empty. And the loop runs until the value of the cell referred to by the Rng variable is empty.

Then, the code finds out the revenue from the sales by simply multiplying the values from the Unit Price and Total Sales columns in each iteration. The Unit Price and Total Sales columns are 1 and 5 cells to the right of the B column, which is indicated by the Offset function. Then, the code stores the value in the Revenue column, which is 6 cells offset from the B column.

Finally, Set Rng = Rng.Offset(1, 0), this line sets the next row in the B column as the value of the Rng variable, and the iteration resumes until the value is empty.

• Click on the Run button in the VBA Editor.
• The revenue of each fruit will be in the Revenue column. (Marked with Green Rectangle)

### 2. Utilizing Offset Function Inside For Next Loop to Find Empty Cells

The code below uses the Offset function inside a For Next Loop to look for empty cells in the dataset and writes Empty if the cell is empty and Not Empty if the cell is not in the Remarks column. ``````Sub Offset_For_Next()
'declaring range and integer variable
Dim i As Integer
Dim Rng As Range
Set Rng = Range("B5")
'finding last non-empty cell
LastValuedCell = Range("B1048576").End(xlUp).Row
' running for loop through the dataset to find empty cells
For i = Rng.Row To LastValuedCell
If Rng.Value <> "" Then
Rng.Offset(0, 6) = "Not Empty"
Set Rng = Rng.Offset(1, 0)
Else
Rng.Offset(0, 6) = "Empty"
Set Rng = Rng.Offset(1, 0)
End If
Next i
End Sub``````

VBA Breakdown

This code looks through the dataset in the Offset_in_ForNextLoop sheet and writes “Empty” if a cell within the dataset does not contain any value and writes “Not Empty” if the cell has a value.

``````Dim i As Integer
Dim Rng As Range
Set Rng = Range("B5")``````

Here, we declare two variables named Rng and i and set the value of the Rng variable to B5.

``LastValuedCell = Range("B1048576").End(xlUp).Row``

It finds the row number of the last non-empty cell in the B column. It starts from the last cell of the sheet in that column, which is B1048576, and then the.End(xlUp) method goes up from there and locates the last non-empty cell and returns its row number.

``````For i = Rng.Row To LastValuedCell
If Rng.Value <> "" Then
Rng.Offset(0, 6) = "Not Empty"
Set Rng = Rng.Offset(1, 0)
Else
Rng.Offset(0, 6) = "Empty"
Set Rng = Rng.Offset(1, 0)
End If
Next i``````

The For Loop runs from 1 to the row number of the last non-blank cell, in this case 12.

The If statement within the For loop checks if the value of the current cell in column B is not empty (i.e., not equal to an empty string “”). If it is not empty, the code will set the value of the cell offset by 6 columns to the right to “Not Empty” using the Offset property. The Rng variable is then updated to refer to the next row in column B by using the Offset method with arguments (1,0), which means one row down and zero columns to the right. Also, if the value in the cell in the B column is empty, then the code writes “Empty” in the Remarks column of the dataset, which is offset by 6 cells from the starting cell in the B column. Then, again, the code sets the value of the Rng variable one row down from the current cell.

• Click on the green rectangle in the VBA Editor to run the code.
• The code will determine if the cell is empty or not and write accordingly in the Remarks column.  (Marked with Green Rectangle)

### 3. Applying Offset Function in Do While Loop to Find the Maximum Sales

The ensuing code finds out the highest sales of each fruit and writes them in the Max Sales column. It also highlights the cell that contains the maximum sales value. ``````Sub Offset_Do_While()
'declaring range variable
Dim Rng As Range
Set Rng = Range("B5")
'running do while loop to iterate through the dataset
Do While Rng.Value <> ""
'setting a range variable to find the highest sales from the range
Set Rng2 = Range(Rng.Offset(0, 2), Rng.Offset(0, 4))
temp_max = -1
'running for loop to get and set the highest sales value
For i = 1 To 3
If Rng2.Cells(1, i) > temp_max Then
temp_max = Rng2.Cells(1, i)
End If
Next i
'for loop to color the background of the highest sales cell
For i = 1 To 3
If Rng2.Cells(1, i) = temp_max Then
Rng2.Cells(1, i).Interior.Color = vbGreen
End If
Next i
'writing the highest cells in the fifth column from the first column
Rng.Offset(0, 5) = temp_max
'going to the next row of the B column
Set Rng = Rng.Offset(1, 0)
Loop
End Sub``````

VBA Breakdown:

``````Dim Rng As Range
Set Rng = Range("B5")``````

The code declares a Range type variable Rng and sets its value to Range(“B5”). That means the range will start from that cell.

``````Do While Rng.Value <> ""
Set Rng2 = Range(Rng.Offset(0, 2), Rng.Offset(0, 4))
temp_max = -1
For i = 1 To 3
If Rng2.Cells(1, i) > temp_max Then
temp_max = Rng2.Cells(1, i)
End If
Next i
For i = 1 To 3
If Rng2.Cells(1, i) = temp_max Then
Rng2.Cells(1, i).Interior.Color = vbGreen
End If
Next i
Rng.Offset(0, 5) = temp_max
Set Rng = Rng.Offset(1, 0)
Loop``````

The Do While Loop first, checks if the value in the B5 cell is empty or not and then runs until the value is empty one.

Set Rng2 = Range(Rng.Offset(0, 2), Rng.Offset(0, 4))  sets a new range in a new variable called Rng2. The cell range is from two columns from the B5 cell, the D5 cell, to four columns from the B5 cell, the F5 cell. So, the range is D5:F5.

Then, it initializes the temporary variable temp_max to -1. The For Loop that follows will go through the values in the D5:F5 range and look for the biggest value among them. The next For Loop simply marks the highest value among the values in the D5:F5 with green color.

The Rng.Offset(0, 5) = temp_max line writes the highest sales value in the G5 cell, which is 5 cells offset from the B5 cell.

Finally, we set the Rng variable value from the present value, B5, to one row down, B6, and the loop continues until the code finds an empty cell in the B column.

• Run the code from the VBA Editor.
• As a result, the maximum sales of each fruit will be written in the Max Sales column.
• The code will also highlight the highest sale cells with green markers.

### 4. Using Offset Function in VBA Loop with ActiveCell Command

The code below will find out the maximum sale among all the fruits and show it in a MsgBox. It will use the ActiveCell property along the way. ``````Sub Offset_with_ActiveCell()
'setting a range variable with activecell command
Set Rng = Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(7, 4))
'setting the first value of the range to the highest value
Highest_Sales = Rng.Cells(1).Value
'running for loop to get the highest sales value
For i = 1 To Rng.Cells.Count
If Rng.Cells(i + 1) > Highest_Sales Then
Highest_Sales = Rng.Cells(i + 1).Value
Else
Highest_Sales = Highest_Sales
End If
Next i
'for loop to color the highest sales cell
For Each Cell In Rng
If Cell = Highest_Sales Then
Cell.Interior.Color = vbGreen
End If
Next Cell
'output
MsgBox "The amount of highest sale is " & Highest_Sales
End Sub``````

VBA Breakdown:

``````Set Rng = Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(7, 4))
Highest_Sales = Rng.Cells(1).Value``````

The first line of the code declares a variable Rng of type Range. The code uses the Offset property with ActiveCell to set the Rng variable to a range of cells that are two columns to the right of and seven rows down from the currently active cell. It then initializes a variable called Highest_Sales to the value of the first cell in the Rng range.

``````For i = 1 To Rng.Cells.Count
If Rng.Cells(i + 1) > Highest_Sales Then
Highest_Sales = Rng.Cells(i + 1).Value
Else
Highest_Sales = Highest_Sales
End If
Next i``````

The code starts with a For loop that iterates from 1 to the total number of cells in the Rng range, which is determined by the Count property of the Cells object within the range. Here loop variable i is used to accessing each cell in the range sequentially. Inside the loop, the code checks if the value of the cell that is one position to the right of the current cell (i.e., Rng.Cells(i + 1)) is greater than the current value of Highest_Sales. If it is, the code updates the value of Highest_Sales to the value of that cell using the Value property of the cell.  If the value of the cell that is one position to the right of the current cell is not greater than Highest_Sales, the code does not update the value of Highest_Sales. Instead, it assigns Highest_Sales to itself, which has no effect on its value.  The loop then continues to the next cell in the Rng range and repeats the process until it has iterated through all the cells in the range.

``````For Each Cell In Rng
If Cell = Highest_Sales Then
Cell.Interior.Color = vbGreen
End If
Next Cell``````

This is a For Each Loop that looks through each cell in the Rng range and then matches their value with the Highest_Sales value. If the value matches, which it obviously will, the code sets the fill color of that cell to green.

``MsgBox "The amount of highest sale is " & Highest_Sales``

This line simply shows the value of the fruit with the highest sales among all the fruits in a MsgBox with a bit of formatting.

• Click on the run button in the VBA Editor to execute the code.
• The B5 cell will be the active cell.
• The maximum sale value will be in the MsgBox and also the cell that contains the value will be highlighted.

### 5. Applying Offset Function in VBA Loop to Find Values Matching Specific Criteria ``````Sub Criteria()
'declaring worksheet variable
Dim ws1 As Worksheet
Dim ws2 As Worksheet
'setting value of the worksheet variable
Set ws1 = ThisWorkbook.Sheets("Studnet_Info")
Set ws2 = ThisWorkbook.Sheets("Query")
Set Rng1 = ws1.Range("B5:E12")
Set Rng2 = ws2.Range("B5")
'running loop in the query datasheet with while loop
While Rng2.Value <> ""
'running for loop in the student info datasheet
For i = 1 To Rng1.Rows.Count
'matching the values from the two sheets
If Rng2.Value = Rng1.Cells(i, 1) Then
Rng2.Offset(0, 1) = Rng1.Cells(i, 2)
Rng2.Offset(0, 2) = Rng1.Cells(i, 3)
Rng2.Offset(0, 3) = Rng1.Cells(i, 4)
End If
Next i
'going to the next row of the B column
Set Rng2 = Rng2.Offset(1, 0)
Wend
End Sub``````

VBA Breakdown:

``````Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = ThisWorkbook.Sheets("Studnet_Info")
Set ws2 = ThisWorkbook.Sheets("Query")
Set Rng1 = ws1.Range("B5:E12")
Set Rng2 = ws2.Range("B5")``````

The code starts by defining two worksheet objects: ws1 and ws2. These objects are used to refer to the “Studnet_Info” and “Query” worksheets, respectively, within the current workbook.

Next, the code sets two range objects: Rng1 and Rng2. Rng1 is set to refer to the range B5:E12 on the “Studnet_Info” worksheet, and Rng2 is set to refer to cell B5 on the “Query” worksheet.

``````While Rng2.Value <> ""
For i = 1 To Rng1.Rows.Count
If Rng2.Value = Rng1.Cells(i, 1) Then
Rng2.Offset(0, 1) = Rng1.Cells(i, 2)
Rng2.Offset(0, 2) = Rng1.Cells(i, 3)
Rng2.Offset(0, 3) = Rng1.Cells(i, 4)
End If
Next i
Set Rng2 = Rng2.Offset(1, 0)
Wend``````

Here, the code will run until it finds an empty cell in the range in the “Query”. Then, it loops through the Rng1 range and matches the values from this range with the current value of the Rng2. If the values match, then it takes the values of marks for that particular individual from Rng1 and writes them to the 1st, 2nd, and 3rd columns of Rng2. Then, the code matches the next value of Rng2 with the Set Rng2 = Rng2.Offset(1, 0) line.

• Initially, the Query sheet will not have any query value.
• Run the code.
• Go to the Query sheet.
• Now, the sheet will contain the marks of the students whose names are on that sheet.
• The code extracted the values from the Student_Info sheet.

## How to Use Offset Function in Excel VBA ``````Sub Offset_Function()
'declaring range variable
Dim Rng As Range
Set Rng = Range("B5")
'selecting cell with offset
Rng.Offset(2, 3).Select
'printing out message in MsgBox
MsgBox "The marks that " & Rng.Offset(2, 0).Value & " acquirs in " & Rng.Offset(-1, 3).Value & " is " & Rng.Offset(2, 3).Value
End Sub``````

VBA Breakdown:

``````Sub Offset_Function()
'declaring range variable
Dim Rng As Range
Set Rng = Range("B5")
'selecting cell with offset
Rng.Offset(2, 3).Select
'printing out message in MsgBox
MsgBox "The marks that " & Rng.Offset(2, 0).Value & " acquirs in " & Rng.Offset(-1, 3).Value & " is " & Rng.Offset(2, 3).Value
End Sub``````

The code selects a cell using the Offset property and then displays a message box that includes information from three other cells that are also located using the Offset property.

The code starts from the B5 cell and then, Rng.Offset(2, 3).Select command line uses the Offset function and selects the E7 cell, which is 2 rows down and 3 columns to the right of the B5 cell.

Finally, the third line uses a message box to display a message that combines information from three cells, which are also located using the Offset property of the Rng range. The first piece of information is the value in the cell that is 2 rows down to the right of B5, which is the name of the student. The second piece of information is the value in the cell that is 1 row up and 3 columns to the right of B5, Math. The third piece of information is the value in the cell that is 2 rows down and 3 columns to the right of B5, which is the mark.

• Run the code by clicking on the run icon.
• The E7 cell will be selected.
• Also, the code will show the student’s name and the subject in which he/she received the mark.

1. What does offset mean in VBA?

In VBA, the “Offset” property is used to refer to a cell or a range of cells that is a specified number of rows and/or columns away from a reference cell or range. The reference cell or range can be specified using a variable or a literal reference to a cell or range.

The syntax for the “Offset” property is as follows:

``Range.Offset(RowOffset, ColumnOffset)``

where “RowOffset” and “ColumnOffset” are the numbers of rows and columns by which the range is to be offset from the reference range.

2. Why would you use offset?

The “Offset” property can be a useful tool for navigating and manipulating cells in a worksheet, especially when the location of the cells you need to work with is relative to another cell or range.

• If you need to move or copy data to a cell that is relative to another cell, you can use the “Offset” property to specify the location of the destination cell.
• If you need to loop through a range of cells in a worksheet, you can use the “Offset” property to move to the next cell in the range.
• If you want to apply conditional formatting to a range of cells based on the value of a nearby cell, you can use the “Offset” property to refer to the nearby cell.

## Things to Remember

• In this article, we have used the Offset function and property interchangeably. In practice, there is an OFFSET function in the Excel sheet which is used in the sheet directly. The Offset function or property is accessible only by VBA code.

## Conclusion

In this article, we have learned about the application of Excel VBA Offset Within Loop. These examples will help the users understand the combination of Offset and the Excel VBA loop properly. In return, they can apply this combination to solve their problems. I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  