How To Fill 2D Arrays Easily

[donna.gough]

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

 

[Alan Beban]

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

 

[donna.gough]

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?

 

[keepITcool]

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

 

[donna.gough]

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

 

[donna.gough]

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.

 

[keepITcool]

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]

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!

 

[donna.gough]

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.

 

[Dave Peterson]

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

 

[Alan Beban]

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.

Not clear exactly what the chronology of events is, and what's going on
in between filling rows in the array, and in between having the filled
multi-row array and then transferring it to a single column on the
worksheet; but in general you might want to consider the functions in
the freely downloadable file at http:/home.pacbell.net/beban. If they
are available to your workbook you can add a horizontal range of data
(assigned to a variable "rng" for example) as a row of data in the first
columns of, for example, row 3 of the array (let's say temparray()),
with something like

ReplaceSubArray temparray, rng, 3, 1

And you can transfer a 2-D array column by column to a single column on
the worksheet (let's say Column H) with something like

arr = ArrayReshape(tempArray, ArrayCount(tempArray), 1, "c")
Range("H1:H" & ArrayCount(tempArray)).Value = arr

If it were clearer what the flow of events is in your procedure, it
would be possible to provide inline code to do what the above UDF's
(ReplaceSubArray and ArrayReshape) would do.

Alan Beban

 

[Alan Beban]

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

 

[Alan Beban]

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

 

[sali]

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!