Offset Method

Range.Offset(RowOffset, ColumnOffset)
This method returns range that is offset to a range object.
This method does not change the active range (unlike the Select and Activate methods).
This enters the number 15 in the cell directly below and to the right of the active cell.

ActiveCell.Range("B2").Value = 15 

The preferred method though is to use the Offset property.

ActiveCell.Offset(rowindex, columnindex).Value = 15 
ActiveCell.Offset(1, 1).Value = 15
ActiveCell.Offset(1, -2).Select
Range(ActiveCell.Offset(1). ActiveCell.Offset(1).End(xlDirection.xlDown)).Select
ActiveCell.Offset(0, 1).Value = "some text"

Using the Offset position (0, 0) will refer to the active cell.

ActiveCell.Offset(0, 0).Value = "some text" 
Range("B2").Offset(4, 4).Value = 20

If you try to Offset to a cell which has in invalid column or row number then an error will be generated.


Referring to the Row Above

Range("B2").Offset(1,0).Address = "B1" 
Range("B2").Offset(1).Address = "B1"

Referring to the Row Below

Range("B2").Offset(1,0).Address = "B3" 
Range("B2").Offset(1).Address = "B3"

Referring to the Next Column

Range("B2").Offset(0, 1).Address = "C2" 
Range("B2").Offset(, 1).Address = "C2"

Referring to the Previous Column

Range("B2").Offset(0, 1).Address = "A2" 
Range("B2").Offset(, 1).Address = "A2"


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext