Using Offset Property in VBA

Offset Property in MS Excel Macro 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: Using the Offset Property to Refer to a Range

Leave a Reply

Your email address will not be published. Required fields are marked *