I have a square range from 5 rows and 10 cells.
How can i address a cell (in a macro) in this range so that i'm able to get
and put values into a cell (adressed by the row and colum).

I know it must be possible but i cant find the command in the helpfile.

Who can help me?

Thank in advance

Reference the appropriate cell using:

cells(row,col)

Where Row and Col are integer values corresponding to the
cell you are referencing.

Example Cell b5 is cells(5,2)

-- Jeff

A bit more flexible, that allows referencing a point into this grid without
necessarily knowing, or caring, about where it is in absolute terms is to
use

rng(x,y)

where rng is a defined range, x is the relative row, and y is the relative
column. SO, rng(3,4) would be row 4 and column 5 within the area defined by
rng.

--

HTH

Bob Phillips

"Jeff C." wrote in message
...
Reference the appropriate cell using:

cells(row,col)

Where Row and Col are integer values corresponding to the
cell you are referencing.

Example Cell b5 is cells(5,2)

-- Jeff

I like this:

dim myCell as range
dim iRow as long
dim iCol as long

set myCell = range("c9")
for irow = 0 to 4 '5 - 1
for icol = 0 to 9 '10 - 1
msgbox mycell.offset(irow,icol).value & _
mycell.offset(irow,icol).address
next icol
next irow

..offset is 0 based .offset(0,0) refers to the same cell.

mycell(x,y) is one's based. mycell(1,1) refers to itself.

Alan Beban has some notes at Chip Pearson's site that you may want to
read/print:

http://www.cpearson.com/excel/cells.htm


Ger wrote:

I have a square range from 5 rows and 10 cells.
How can i address a cell (in a macro) in this range so that i'm able to get
and put values into a cell (adressed by the row and colum).

I know it must be possible but i cant find the command in the helpfile.

Who can help me?

Thank in advance

--

Dave Peterson

Think you are off by one (since this notation is one's based)

? Range("A1:Z26")(3,4).row
3
? Range("A1:Z26")(3,4).Column
4

so rng(3,4) would be cell at the 3rd row and 4th column with the first cell
in rng as the base (1,1) location.

Regards,
Tom Ogilvy


Bob Phillips wrote in message
...
A bit more flexible, that allows referencing a point into this grid
without
necessarily knowing, or caring, about where it is in absolute terms is to
use

rng(x,y)

where rng is a defined range, x is the relative row, and y is the relative
column. SO, rng(3,4) would be row 4 and column 5 within the area defined
by
rng.

--

HTH

Bob Phillips

"Jeff C." wrote in message
...
Reference the appropriate cell using:

cells(row,col)

Where Row and Col are integer values corresponding to the
cell you are referencing.

Example Cell b5 is cells(5,2)

-- Jeff

Mea culpa!

I know you're right, I knew so before I wrote it, but I still wrote it with
the Offset value.

Bob


"Tom Ogilvy" wrote in message
...
Think you are off by one (since this notation is one's based)

? Range("A1:Z26")(3,4).row
3
? Range("A1:Z26")(3,4).Column
4

so rng(3,4) would be cell at the 3rd row and 4th column with the first
cell
in rng as the base (1,1) location.

Regards,
Tom Ogilvy


Bob Phillips wrote in message
...
A bit more flexible, that allows referencing a point into this grid
without
necessarily knowing, or caring, about where it is in absolute terms is
to
use

rng(x,y)

where rng is a defined range, x is the relative row, and y is the
relative
column. SO, rng(3,4) would be row 4 and column 5 within the area defined
by
rng.

--

HTH

Bob Phillips

"Jeff C." wrote in message
...
Reference the appropriate cell using:

cells(row,col)

Where Row and Col are integer values corresponding to the
cell you are referencing.

Example Cell b5 is cells(5,2)

-- Jeff