Offset Property

Offset Property

Following are different ways to select a cell using the offset method. The way it works is that there are two numbers that are the relationship between the current active cell; the first number is the row offset and the second is the column offset.

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Range("B2").Offset(1,0).Select ‘Moves one cell down
Range("B2").Offset(-1,0).Select ‘Moves one cell up
Range("B2").Offset(0,1).Select ‘Moves one cell right
Range("B2").Offset(0,-1).Select ‘Moves one cell left
[/sourcecode]


The following is the Syntax of using the offset property

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
object.Offset(rowOffset, columnOffset) ‘Syntax
[/sourcecode]


Using the Offset method to refer to a range

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
‘Enters a value of 15 into the cell above the active cell
ActiveCell.Offset(-1, 0).Value = 15

‘Assuming B1 ActiveCell, adds data to B1:B3
Sub Macro1()
ActiveCell = 1
ActiveCell.Offset(1, 0) = 2
ActiveCell.Offset(2, 0) = 3
End Sub
[/sourcecode]


Filling a Range

The following macro uses the offset property to fill a range. As seen below, it fills a 10X10 range with consecutive numbers beginning with the number 1 (Num Variable).

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Option Explicit

Sub FillRangeUsingOffset()
Dim Num As Integer, row As Integer, col As Integer
Num = 1
For row = 0 To 9
For col = 0 To 9
Sheets("Sheet1").Range("B2").Offset(row, col).Value = Num
Num = Num + 1
Next col
Next row
End Sub
[/sourcecode]