I have a named area "Stock" on a spreadsheet (Office XP), the range is defined as follows:
=OFFSET('Stock List'!$K$1,0,0,COUNTA('Stock List'!$F:$F),4)
ie it starts at K1, is 4 cols wide and as deep as the number of used cells in Col F
I want to access this in an array format using VBA, ie using variables to define the
position of a single cell within the array and to either read or set the value of that cell.
I tried
Range("Stock").Offset(x,y).value="test"
to put 'test' into cell x,y in the array - it puts the value into a range, not just a cell.
and to get the value i.e.
variable=Range("Stock").Offset(x,y).value
What is the correct code please
ALlan
How to adress a single cell in a named area
ducmis
To get at a cell within the named range Stock use this
Dim x, y
x = 2
y = 3
Range("Stock").Cells(x, y).Value = "test"
With you code the offset was only changing the start point of cell reference and all the cells were being changed.
mh1hep
Thanks Andy
Re cross-posting
you are quite correct of course
I posted here only 'cos I'd lost the Oz link, serves me right for playing with Vista, system crashed and links lost.
I find Oz usually gets a reply quicker, and [posted there after giving this almost 24hrs and me time to re-find the Oz link