Excel VBA Offset Within Loop (4 Examples)

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.

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.

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.

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.

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.

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.