Two dimensional dynamic array

Question

Im having Troubles populating a two-dimensional Array on the fly.

I have a list of 26 names and want to randomly divide them into 5 Groups of 5each, with one having 6. I then want to use each Group to write the names into a separate sheet. My Background is PHP and JS, so the Need to dim or redim an Array doesnt come as easy to me.

Dim KlasseA() As String
Dim KlasseB() As String
Dim KlasseC() As String
Dim KlasseD() As String
Dim KlasseE() As String


For i = 1 To 20
   If Sheets(1).Cells(i + 1, 2) <> "" Then      

        pick = ((5 - 1 + 1) * Rnd + 1)

        If pick = 1 Then
            KlasseA(i) = Sheets(1).Cells(i + 1, 3) & ", " & Sheets(1).Cells(i + 1, 2)
        ElseIf pick = 2 Then
            KlasseB(i) = Sheets(1).Cells(i + 1, 3) & ", " & Sheets(1).Cells(i + 1, 2)
        ElseIf pick = 3 Then
            KlasseC(i) = Sheets(1).Cells(i + 1, 3) & ", " & Sheets(1).Cells(i + 1, 2)
        ElseIf pick = 4 Then
            KlasseD(i) = Sheets(1).Cells(i + 1, 3) & ", " & Sheets(1).Cells(i + 1, 2)
        ElseIf pick = 5 Then
            KlasseE(i) = Sheets(1).Cells(i + 1, 3) & ", " & Sheets(1).Cells(i + 1, 2)
        End If
    Else
        Exit For
    End If
Next i

' push the 5 Arrays into a master Array. Use 1 Loop inside a Loop Output the 5 Groups to 5 sheets

Im having two Problems here:

How can i create "higher" Array where i can push the 5 lower Arrays into, to then Loop over all the names (i.e. one final Loop instead of 5).
The code above will populate the sub-Arrays in a non-fluid manner, i.e. a bunch of array-index are left out (due to the i).

Ideally, i would want to create a 2d-Array and use this inside the Loop instead of the flawed workaround above. Can someone advice me ?

Shuffle your array using cpearson.com/excel/ShuffleArray.aspx and then just loop over it and place the values on the sheets - five each sheet then six on the final one. — Tim Williams
– Tim Williams, Commented Jun 24, 2016 at 16:11

user4039065 · Accepted Answer · 2016-06-24 08:55:44Z

0

This is one of those cases when sample data together with expected results would have gone a long way towards explaining the situation. This is what I envision you are attempting.

Option Explicit

Sub mcrKlasse()
    Dim Klasse() As Variant
    Dim i As Long, pick As Integer

    ReDim Klasse(1 To 5, 1 To 20)

    With Worksheets(1)
        For i = LBound(Klasse, 2) To UBound(Klasse, 2) 'for 1 to 20
            If CBool(Len(.Cells(i + 1, 2).Value)) Then
                pick = ((5 - 1 + 1) * Rnd + 1)
                Select Case pick
                    Case 1, 2, 3, 4, 5
                        Klasse(pick, i) = Join(Array(.Cells(i + 1, 3), .Cells(i + 1, 2)), ", ")
                End Select
            Else
                Exit For
            End If
        Next i

        'redimension the array in case less than 20 were filled
        ReDim Preserve Klasse(1 To 5, 1 To i - 1)

        'stick them back on the worksheet
        With .Cells(Rows.Count, 2).End(xlUp).Offset(2, 0)
            .Resize(UBound(Klasse, 1), UBound(Klasse, 2)) = Klasse
        End With

        'flip them (transpose) and stick them back on the worksheet
        With .Cells(Rows.Count, 2).End(xlUp).Offset(2, 0)
            .Resize(UBound(Klasse, 2), UBound(Klasse, 1)) = _
                Application.Transpose(Klasse)
        End With

    End With

End Sub

The code runs through the data but tbh, I'm not entirely clear on what you are trying to accomplish.

answered Jun 24, 2016 at 8:55

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

user431806 Over a year ago

Basicly, i have a sheet of of 26 names and need to randomly divide them into 5 Groups, whereas the 5 Groups should have the same amount of members, if possible. I know realize that randomly picking the new Group does not result in Groups with the same size. thanks

Collectives™ on Stack Overflow

Two dimensional dynamic array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related