QUESTION ON "OFFSET" !

  • Thread starter Thread starter jay dean
  • Start date Start date
J

jay dean

When I call "offset(some number)" on some defined range as in "Set rng
= rng.offset(5)" it offsets the range downwards.

How should I code it if I want the range to :

(1) offset(5) to the right

(2) offset(5) upwards.

Any assistnce would be appreciated. Thanks.

Jay Dean
 
Jay,

1) ActiveCell.Offset(0, 5).Select

2) ActiveCell.Offset(-5,0).Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Hi Jay,
From Help.
expression.Offset(RowOffset, ColumnOffset)

expression Required. An expression that returns a Range object.

RowOffset Optional Variant. The number of rows (positive, negative, or 0
(zero)) by which the range is to be offset. Positive values are offset
downward, and negative values are offset upward. The default value is 0.

ColumnOffset Optional Variant. The number of columns (positive, negative,
or 0 (zero)) by which the range is to be offset. Positive values are offset
to the right, and negative values are offset to the left. The default value
is 0.

So offset(5), no column specified, moves 5 rows down.
offset(-5) moves up 5 rows.
offset(,5) moves right 5.
offset(-5,5) moves up 5 and right 5.
 
Jay,

Offset has a row and a column argument. And, it can take negative numbers.
So you can use

.Offset(3,1)

which references 3 rows down, 1 column to the right, or

.Offset(-3,-1)

which references 3 rows up and 1 column to the left.

Just be careful that you don't reference an invalid range. For instance, if
you are in cell A3, and you use

.Offset(0,-1)

you will get an error as you are trying top reference a non-existent range.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top