Free Excel training videos

Offset Property in MS Excel

Offset property in Excel: Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
The syntax is: OFFSET(reference,rows,cols,height,width).
Reference
is the reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
Rows
is the number of rows, up or down, that you want the active or selected cell to refer to. Using 2 as the rows argument specifies that the active cell in the reference is two rows below reference. Rows can be positive which means below the starting reference or negative which means above the starting reference.
Cols
is the number of columns, to the left or right, that you want the active cell of the result to refer to. Using 3 as the cols argument specifies that the selected cell in the reference is three columns to the right of reference. Cols can be positive which means to the right of the starting reference or negative which means to the left of the starting reference.
Height is the height, in number of rows, that you want the returned reference to be. Height must be a positive number.
Width is the width, in number of columns, that you want the returned reference to be. Width must be a positive number.
If height or width is omitted, it is assumed to be the same height or width as reference.

If rows and cols offset reference over the edge of the worksheet, OFFSET returns the #REF! error value. F
or example, ActiveCell.Offset(0, 1) selects a cell in the same row and a column to the right of the active cell. Active cell is the cell where your cursor is or the selected cell.
The code 'ActiveCell.Offset(1, -2)' moves your cursor to the next row from the last position and 2 columns to the left from there. The code '=OFFSET(C3,2,3,1,1) references the value in cell F5.



The code used in the training video is given below for your ready reference:

Sub myoffset()
Dim reply As String
 reply = "yes"
 
 Do While reply = "yes"
 ActiveCell = Application.InputBox("Enter a name: ", _
 "MyName", , , , , 2)
 ActiveCell.Offset(0, 1).Select
 ActiveCell = Application.InputBox("Enter the salary: ", _
 "MySalary", , , , , 1)
 ActiveCell.Offset(0, 1).Select
 ActiveCell = Application.InputBox("Enter Perks: ", _
 "MyPerks", , , , , 1)
 ActiveCell.Offset(1, -2).Select
 reply = InputBox("Do you want to continue(yes/no)")
 If reply = "no" Then End
 Loop


End Sub

Further Reading
Offset property in Excel