Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Array within an array (2002 excel)

    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

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Array within an array (2002 excel)

    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

  3. #3
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array within an array (2002 excel)

    <pre>Dim myArray as Variant
    myArray = Array(ArrCrew1Days, ArrCrew2Days,...)</pre>

    is one way to do what you describe.

    Using a 2-dimemsional array is another.
    <pre>Dim myArray as Variant
    myArray = Range("A1:H10").Value</pre>

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

  4. #4
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Array within an array (2002 excel)

    Thanks,

    This worked great.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •