an offset

In actual work, in addition to the way to obtain the cell directly through [a1], you can also use the offset to obtain it indirectly, as shown in the following code. The purpose of this code is to calculate the total price.

Sub Calculate the total price()

Dim myrange As Range
For Each myrange In Range("a2:a5")
'Offset directly, starting at 1
myrange(1, 3) = myrange.Value * myrange(1, 2).Value
'use offset，start from 0
myrange.Offset(0, 3) = myrange.Value * myrange.Offset(0, 1).Value * 1.5
Next

End Sub

Two resize

When you want to get an area indirectly, you can use resize. It can input 2 parameters, one is the row offset and the other is the column offset. Note that resize starts at 1, and offset starts at 0. resize gets the area, and offset gets the cell.

The figure below moves the data in column C larger than 30 to column E by using resize.

Sub ResizeSub()

Dim myrange As Range
For Each myrange In Range("a2:a5")
    If myrange(1, 3).Value > 30 Then
        myrange(1, 5).Resize(1, 3).Value = myrange.Resize(1, 3).Value
    End If
Next

End Sub

three range

Use range to get the range indirectly. In normal work, we often use range to directly obtain the area, but in fact, the range can also indirectly obtain the specified area. As shown in the code below, the purpose is still to copy the data whose total price is greater than 30 to columns E~G.

When we use Range("a1:c1").Select directly, the three cells a1~c1 in the sheet will be selected. At this time, it can be regarded as a base point similar to (0,0) to obtain a1~ c1.

When the cell is not at the base point, such as the following example at [a4]. At this time, using Range will use [a4] as a new base point, that is, starting from [a4], it is a brand new sheet.

Off topic: range is not limited to getting a single area, we can also use range to get multiple areas, such as Range("a1:b2,d3:e5,f8").

Sub test20()

Dim myrange As Range
For Each myrange In Range("a2:a5")
    If myrange.Range("c1").Value > 30 Then
        myrange.Range("e1:g1").Value = myrange.Range("a1:c1").Value
    End If
Next

End Sub

four union s

Use union to get the merged region. Still using the above data, this time, after obtaining the data with a total price greater than 30, copy it to the first line uniformly. The code and the effect after running are as follows.

Sub test20()

Dim myrange As Range, result_range As Range
For Each myrange In Range("a2:a5")
    If myrange.Range("c1").Value > 30 Then
        If result_range Is Nothing Then
            Set result_range = myrange.Range("a1:c1")
        Else
            Set result_range = Application.Union(result_range, myrange.Range("a1:c1"))
        End If
    End If
Next

result_range.Select
[e1].Resize(2, 3).Value = result_range.Value

End Sub

five intersect

Contrary to union, the function of intersect is to obtain intersection. The code and renderings are shown below. At present, I want to know the sales of products with a unit price of 15 in February.

Sub test20()

Dim myrange As Range, row_range, column_range, result_range
'get row
For Each myrange In Range("a2:a5")
    If myrange.Value = [g1].Value Then Set row_range = myrange.EntireRow
Next
'get column
For Each myrange In Range("b1:d1")
    If myrange.Value = [g2].Value Then Set column_range = myrange.EntireColumn
Next
'intersection
Set result_range = Application.Intersect(row_range, column_range)
[g3].Value = result_range.Value

End Sub