|
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).Range("A1").Select
ActiveCell = Application.InputBox("Enter the salary: ", _
"MySalary", , , , , 1)
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell = Application.InputBox("Enter Perks: ", _
"MyPerks", , , , , 1)
ActiveCell.Offset(1, -2).Range("A1").Select
reply = InputBox("Do you want to continue(yes/no)")
If reply = "no" Then End
Loop
End Sub
|