OFFSET

[hhenrion]

Hi experts,

I want to select a range of size 3 x 4, located 5 lines and 1 row off the reference cell (let's say cell G10).

How do I write that using OFFSET keyword ?

Thanks a lot,
regards,

H.

 

[GlennUK]

size 3 x 4 equals what exactly ... 3 rows x 4 columns, or 3 columns x 4 rows?

And location ... 5 lines and 1 row means what? A line is a row, isn't it. Which of these refers to columns?

The format is:
=OFFSET(refcell,rows_to_offset,cols_to_offset,return_size_in_rows,return_size_in_cols)
which is in the help actually.

 

[NBVC]

Something like:

OFFSET(G10,5,1,3,4)

but you need to specify in which direction you are counting rows and columns and you need to know if the matrix is 3 high or 4 high by 3 wide or 4 wide.

Also, this function has to be used within another to work..or you will get a #Value error..


e.g. =Sum(OFFSET(G10,5,1,3,4)) will sum the values in your matrix.

 

[GlennUK]

Just realised that this query may be about VBA ... "select a range" ... "OFFSET keyword" .... is that the case?

 

[hhenrion]

Glenn,

actually... YES (sorry I was unclear, this is the end opf the day...).

I am in VBA trying to select and area of i rows and j columns, and the top left cell of the area is ii rows and jj columns of the reference cell.

Thanks in advance,

H.

 

[GlennUK]

Use Offset(rows,cols).Resize(numrows,numcols)

 

[hhenrion]

Thx Glenn,

H.

 

[GlennUK]

My pleasure.