Referring to ranges

Using the Offset property to refer to a range

You’ve already seen a reference to Offset when you recorded a relative reference. Offset enables you to manipulate a cell based on the location of another cell, such as the active cell. Therefore, you do not need to know the address of the cell you want to manipulate.

The syntax for the Offset property is as follows:

Range.Offset(RowOffset, ColumnOffset)

For example, the following code affects cell F5 from cell A1:

Range("A1").Offset(RowOffset:=4, ColumnOffset:=5)

Or, shorter yet, you can write this:

Range("A1").Offset(4,5)

The count of the rows and columns starts at A1 but does not include A1.

If you need to go over only a row or a column, but not both, you don’t have to enter both the row and the column parameters. To refer to a cell one column over, use one of these lines:

Range("A1").Offset(ColumnOffset:=1)
Range("A1").Offset(,1)

Both of these lines have the same meaning, so the choice is yours. If you use the second line, make sure to include the comma so Excel knows that the 1 refers to the ColumnOffset argument. Referring to a cell one row up is similar:

Range("B2").Offset(RowOffset:=-1)
Range("B2").Offset(-1)

Once again, you can choose which one to use. It’s a matter of the readability of the code.

Suppose you have a list of produce in column A, with totals next to the produce items in column B. If you want to find any total equal to zero and place LOW in the cell next to it, do this:

Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole, _
    LookIn:=xlValues)
Rng.Offset(, 1).Value = "LOW"

When used in a Sub and looping through a data set, it would look like this:

Sub FindLow()
  With Range("B1:B16")
     Set Rng = .Find(What:="0", LookAt:=xlWhole, LookIn:=xlValues)
     If Not Rng Is Nothing Then
         firstAddress = Rng.Address
         Do
           Rng.Offset(, 1).Value = "LOW"
           Set Rng = .FindNext(Rng)
         Loop While Not Rng Is Nothing And Rng.Address <> firstAddress
    End If
  End With
End Sub

The LOW totals are noted by the program, as shown in Figure 3-1.

FIGURE 3-1 The code puts “LOW” next to the zeros in the data set.

Offsetting isn’t only for single cells; you can use it with ranges. You can shift the focus of a range over in the same way you can shift the active cell. The following line refers to B2:D4 (see Figure 3-2):

Range("A1:C3").Offset(1,1)

FIGURE 3-2 Offsetting the original range A1:C3 by one row and one column references a new range, B2:D4.