Home > Articles > Home & Office Computing > Microsoft Applications

This chapter is from the book

Using the Offset Property to Refer to a Range

You've already seen a reference to Offset; the macro recorder used it when we were recording a relative reference. It enables you to manipulate a cell based off the location of the active cell. In this way, you don't have to know the address of a cell.

The syntax for the Offset property is

Range.Offset(RowOffset, ColumnOffset)

To affect cell F5 from cell A1, write

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

or, shorter yet:

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

The count starts at A1, but does not include A1.

But what 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 column parameter. If you need to refer to a cell one column over, use one of these:

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

Both lines mean the same. The choice is yours. Referring to a cell one row up is similar:

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

Once again, the choice is yours. It is a matter of readability of the code.

Let's say we had a list of produce with totals next to them. Find any total equal to zero and place LOW in the cell next to it. You could do it this way:

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

The LOW totals are quickly noted by the program, as shown in Figure 3.1.

Figure 3.1Figure 3.1 Find the produce with the 0 total.

Offsetting isn't only for single cells—it can be used 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.2Figure 3.2 Offsetting a range—Range("A1:C3"). Offset(1,1). Select.

  • + Share This
  • 🔖 Save To Your Account
VBA and Macros for Microsoft Excel

This chapter is from the book

VBA and Macros for Microsoft Excel

Discussions

comments powered by Disqus