How To Fill 2D Arrays Easily
 

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

How To Fill 2D Arrays Easily
 

How To Fill 2D Arrays Easily
 

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?

How To Fill 2D Arrays Easily
 

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?

How To Fill 2D Arrays Easily
 

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

How To Fill 2D Arrays Easily
 

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.

How To Fill 2D Arrays Easily
 

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

How To Fill 2D Arrays Easily
 

in that case I'd take a different route..

if you do not know the size you need beforehand
i'd dispense with the arrays and use a dictionary
object to collect the data.


set a reference to Microsoft Scripting Runtime

Sub DumpIn1Column()
Dim d As New Scripting.Dictionary
Dim r As Variant, c As Range, i As Long
For Each r In Array(Range("b9:l9"), Range("b14:l14"))
For Each c In r.Cells
If Len(c) Then
i = i + 1
d.Add i, c.Value
End If
Next
Next
Range("z1").Resize(UBound(d.Items) + 1, _
1) = Application.Transpose(d.Items)

End Sub

note:
application.Transpose allows for 5761 items in all versions
more (unlimited) in xl2002 and xl2003, but looking at your question
it should be more than enough.


no more *extra* questions.. please!




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


wrote :

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.

How To Fill 2D Arrays Easily
 

Thanks for the replys.
Unfortunately I am off home now so I will continue with this on monday
and no doubt be continuing my postings!
Thanks again.

How To Fill 2D Arrays Easily
 

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

How To Fill 2D Arrays Easily
 

How To Fill 2D Arrays Easily
 

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

How To Fill 2D Arrays Easily
 

The code below will ignore blanks in the ranges. To transfer the blanks
to the array, delete the 2 lines

If Len(c) Then and
End If

Alan Beban

keepITcool wrote:
in that case I'd take a different route..

if you do not know the size you need beforehand
i'd dispense with the arrays and use a dictionary
object to collect the data.


set a reference to Microsoft Scripting Runtime

Sub DumpIn1Column()
Dim d As New Scripting.Dictionary
Dim r As Variant, c As Range, i As Long
For Each r In Array(Range("b9:l9"), Range("b14:l14"))
For Each c In r.Cells
If Len(c) Then
i = i + 1
d.Add i, c.Value
End If
Next
Next
Range("z1").Resize(UBound(d.Items) + 1, _
1) = Application.Transpose(d.Items)

End Sub

note:
application.Transpose allows for 5761 items in all versions
more (unlimited) in xl2002 and xl2003, but looking at your question
it should be more than enough.


no more *extra* questions.. please!




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


wrote :


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.

How To Fill 2D Arrays Easily
 

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!