Array within an array (2002 excel)

Topic

New Reply

Hi all,

I have 8 arrays of string data within a procedure that I collected from various ranges on a worksheet, each element of the array is defined by a range(s) on my worksheet, what I would like to do is create another array and place all 8 single arrays into it so that I would have one array that is 8 columns by 10 rows. I haven’t been able to achieve what I would like to do, since all the 8 single arrays are already declared, in my “Arrall” how would I place those 8 arrays into it? Is this good practice or should I take another approach, because what I would like to do eventually is place it all on a worksheet, or listbox.

Thanks
Darryl.

just a snippet of my sub

Public Sub daysarr()
Dim ArrCrew1Days(10) As String
Dim ArrCrew2Days(10) As String
Dim ArrCrew3Days(10) As String
Dim ArrCrew4Days(10) As String
Dim ArrCrew5Days(10) As String
Dim ArrCrew6Days(10) As String
Dim ArrCrew7Days(10) As String
Dim ArrCrew8Days(10) As String
Dim Arrall(10,10,10,10,10,10,10,10) as string
End Sub

Reply | Quote

Replies

Your declaration for Arrall() is incorrect.
It should be: Dim Arrall(0 to 10, 0 to 7) ’11 rows by 8 columns
However a slightly different approach may work best for you…
‘–
Public Sub daysarr()
Dim ArrCrew1Days(10) As String
Dim ArrCrew2Days(10) As String
Dim ArrCrew3Days(10) As String
Dim ArrCrew4Days(10) As String
Dim ArrCrew5Days(10) As String
Dim ArrCrew6Days(10) As String
Dim ArrCrew7Days(10) As String
Dim ArrCrew8Days(10) As String
Dim Arrall As Variant

‘code to fill the arrays goes here

Arrall = Array(ArrCrew1Days, ArrCrew2Days, ArrCrew3Days, ArrCrew4Days, _
ArrCrew5Days, ArrCrew6Days, ArrCrew7Days, ArrCrew8Days)

Range(“A13:H23”).Value = Application.Transpose(Arrall)
End Sub
‘–
Jim Cone
Portland, Oregon USA

Reply | Quote

Thanks,

This worked great.

Reply | Quote

Dim myArray as Variant
myArray = Array(ArrCrew1Days, ArrCrew2Days,...)

is one way to do what you describe.

Using a 2-dimemsional array is another.

Dim myArray as Variant
myArray = Range("A1:H10").Value

If you use the 2D array approach, the WorksheetFunction. Index can be used to extract whole rows or colums from myArray.

Reply | Quote