Good Morning Everybody,
I'm sure I've seen this in a post somewhere but for the life of me I
can not find it ! so here goes.

I have a 2D array...

Dim testarray()
ReDim testarray(1 To 5, 1 To 20)

testarray() = Range("B9:L9").Value

The above line fills the 1st row of the array, BUT HOW DO I FILL THE
THE OTHER ARRAY ROWS USING CELL RANGES ?

Thanks for your help....again

Donna

wrote:
Good Morning Everybody,
I'm sure I've seen this in a post somewhere but for the life of me I
can not find it ! so here goes.

I have a 2D array...

Dim testarray()
ReDim testarray(1 To 5, 1 To 20)

testarray() = Range("B9:L9").Value

The above line fills the 1st row of the array, BUT HOW DO I FILL THE
THE OTHER ARRAY ROWS USING CELL RANGES ?

Thanks for your help....again

Donna

Well, it doesn't *fill* the first row of the array, which is 20 elements
long and you're filling only the first 13 elements of that row. But be
that as it may, you *fill* the other rows essentially the same way:

testarray() = Range("B9:L13")

Alan Beban

So, I've filled my first row with...
testarray() = Range("B9:L13")

If I so the following for my 2nd row....
testarray() = Range("B14:L14")

it overwrites my 1st row of data.

So, what syntax should i be using?

Donna,
do not dimension yoiur array
but use 'simple' variant.

dim arrHOR as VARIANT
dim arrVER as variant
dim arrSQR as variant

arrHOR = Range("a1:f1").Value
fills the variant with a 1 dimensional array

arrVER = Range("a1:a10").Value
arrSQR = Range("a1:f10") .Value
both fill the variant with a 2 dimensional array


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


wrote :

So, I've filled my first row with...
testarray() = Range("B9:L13")

If I so the following for my 2nd row....
testarray() = Range("B14:L14")

it overwrites my 1st row of data.

So, what syntax should i be using?

I'm sorry I've told you the wrong thing! (Early morning blues)
I've hust tried to see how your posts would help me.......and noticed
I've posted totally the wrong scenario.
My apologies.

Dim testarray()
ReDim testarray(1 To 5, 1 To 256)

testarray() = Range("B9:L9").Value
testarray() = Range("B14:L14").Value

End Sub

This is what my first post should have been where the 2nd line
ovrwrites the array data.
I want to copy 5 ranges into my Array. The length of each range is
identical but not known before hand hence 1 to 256.

Sorry to bother you again. I can do it by copying the ranges to a temp
sheet where they are all coincident and then put them into the array
but want to avoid this if possible.
Cheers Chaps.
Donna

Extra Question:
I then want to paste all Array data into one long column in the
worksheet.
Can this be done by pasting an array column by the next array column
or not.
Again I know i can do it with nested FOR statements easily but want to
understand alternative methods there are of inputting and retreiving
sets of data from Arrays.

you'd need a 25 'row' array to copy
5 times a 5 row range.

filling it as 1 large array you can only do it
in a loop

untested..

dim rng,iSet&,iRow&,iCol&
redim arr(1 to 25,1 to 256)

for each rng in array(Range("B9:L9"),Range("B14:L14"),etc)
With rng
For irow=1 to 5
For icol=1 to .columns.count
arr((iset)*5 +irow,icol) = .cells(irow,icol)
Next
Next
end with
iset=iset+1
next


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


wrote :

I'm sorry I've told you the wrong thing! (Early morning blues)
I've hust tried to see how your posts would help me.......and noticed
I've posted totally the wrong scenario.
My apologies.

Dim testarray()
ReDim testarray(1 To 5, 1 To 256)

testarray() = Range("B9:L9").Value
testarray() = Range("B14:L14").Value

End Sub

This is what my first post should have been where the 2nd line
ovrwrites the array data.
I want to copy 5 ranges into my Array. The length of each range is
identical but not known before hand hence 1 to 256.

Sorry to bother you again. I can do it by copying the ranges to a
temp sheet where they are all coincident and then put them into the
array but want to avoid this if possible.
Cheers Chaps.
Donna

wrote in message
oups.com...
I'm sorry I've told you the wrong thing! (Early morning blues)
I've hust tried to see how your posts would help me.......and noticed
I've posted totally the wrong scenario.
My apologies.

Dim testarray()
ReDim testarray(1 To 5, 1 To 256)

testarray() = Range("B9:L9").Value
testarray() = Range("B14:L14").Value

End Sub

This is what my first post should have been where the 2nd line
ovrwrites the array data.
I want to copy 5 ranges into my Array. The length of each range is
identical but not known before hand hence 1 to 256.


try this:
dim testaray(5) as variant
testaray(1)=range("a1:a100").value
testaray(2)=range("b1:b250").value

run code with debug ["step_into"] and watch the "locals" window.

idea is to have "array of arrays", so each row may have different size.
since this is "array of arrays", to access data you need [for example]:
testaray(1)(99)
you are not limited to fixed rows*cols matrix!

I think arrVer is still two dimensions:
arrHOR = Range("a1:f1").Value
fills the variant with a 1 dimensional array

But if the range is small (or you're running xl2002+), then this gave me a one
dimensional array.

With Application
arrHOR = .Transpose(.Transpose(Range("a1:f1").Value))
End With



keepITcool wrote:

Donna,
do not dimension yoiur array
but use 'simple' variant.

dim arrHOR as VARIANT
dim arrVER as variant
dim arrSQR as variant

arrHOR = Range("a1:f1").Value
fills the variant with a 1 dimensional array

arrVER = Range("a1:a10").Value
arrSQR = Range("a1:f10") .Value
both fill the variant with a 2 dimensional array

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

wrote :

So, I've filled my first row with...
testarray() = Range("B9:L13")

If I so the following for my 2nd row....
testarray() = Range("B14:L14")

it overwrites my 1st row of data.

So, what syntax should i be using?

--

Dave Peterson

Dave Peterson wrote:
I think arrVer is still two dimensions:
arrHOR = Range("a1:f1").Value
fills the variant with a 1 dimensional array

I think "1 dimensional" was a typo; intended "2 dimensional"

Alan Beban

But if the range is small (or you're running xl2002+), then this gave me a one
dimensional array.

With Application
arrHOR = .Transpose(.Transpose(Range("a1:f1").Value))
End With