Failing to use collections

Question

I have 3 columns with 7 rows of numbers in each. My code is supposed to:

1. Identify which column I selected
2. Get all numbers from one of the columns I didnt select and add to a collection (In order. This is important)
3. Get all numbers from the column I selected and add them to the collection (Also in order)
4. Get all numbers from the remaining column and add it to the collection.
5. In order, from first column to last, from first row to last, place the items that were inside the collection

However, it seems that every time it gets some kind of random order.

It is my first time using collections. Can anyone spot what I did wrong?

Sample data

What should happen

What actually happens

And of course, the culprit

Option Explicit
Public vez As Long
Public grupo As New Collection

Sub magic(coluna As Long)
Dim row As Long
Dim col As Long
Dim i As Long
Set grupo = Nothing
If vez < 3 Then
    If coluna = 1 Then
        For row = 1 To 7
            grupo.Add Cells(row, 2)
        Next row
        For row = 1 To 7
            grupo.Add Cells(row, 1)
        Next row
        For row = 1 To 7
            grupo.Add Cells(row, 3)
        Next row
    ElseIf coluna = 2 Then
        For row = 1 To 7
            grupo.Add Cells(row, 1)
        Next row
        For row = 1 To 7
            grupo.Add Cells(row, 2)
        Next row
        For row = 1 To 7
            grupo.Add Cells(row, 3)
        Next row
    ElseIf coluna = 3 Then
        For row = 1 To 7
            grupo.Add Cells(row, 2)
        Next row
        For row = 1 To 7
            grupo.Add Cells(row, 3)
        Next row
        For row = 1 To 7
            grupo.Add Cells(row, 1)
        Next row
    End If
    i = 1
    For row = 1 To 7
        For col = 1 To 3
            Cells(row, col) = grupo.Item(i)
            i = i + 1
        Next col
    Next row
    vez = vez + 1
End If
End Sub

Answer 1

For my next magic trick I'm going to make all those For row = 1 To 7 dissapear

Alakazam!!!!

Option Explicit
Public vez As Long
Public grupo As New Collection

Sub magic(coluna As Long)
    Dim i As Long
    Dim c As Range, Source As Range
    Set Source = Range(Choose(coluna, "B1:B7,A1:A7,C1:C7", "A1:A7,B1:B7,C1:C7", "B1:B7,C1:C7,A1:A7"))
    Set grupo = New Collection
    For Each c In Source
        grupo.Add c.Value
    Next

    For Each c In Source
        grupo.Add c.Value
    Next

    For Each c In Range("A1:C7")
        i = i + 1
        c.Value = grupo.Item(i)
    Next

    vez = vez + 1
End Sub

Answer 2

Edit: Check @YowE3K answer. It works perfectly with collections instead of arrays.

Replaced collections with an array. Arrays seems easier to work.

Option Explicit
Public vez As Long


Sub magic(coluna As Long)
Dim grupo(1 To 21) As String
Dim row As Long
Dim col As Long
Dim i As Long
i = 1
If vez < 2 Then
    If coluna = 1 Then
        For row = 1 To 7
            grupo(i) = Cells(row, 3).Value
            i = i + 1
        Next row
        For row = 1 To 7
            grupo(i) = Cells(row, 1).Value
            i = i + 1
        Next row
        For row = 1 To 7
            grupo(i) = Cells(row, 2).Value
            i = i + 1
        Next row
    ElseIf coluna = 2 Then
        For row = 1 To 7
            grupo(i) = Cells(row, 1).Value
            i = i + 1
        Next row
        For row = 1 To 7
            grupo(i) = Cells(row, 2).Value
            i = i + 1
        Next row
        For row = 1 To 7
            grupo(i) = Cells(row, 3).Value
            i = i + 1
        Next row
    ElseIf coluna = 3 Then
        For row = 1 To 7
            grupo(i) = Cells(row, 2).Value
            i = i + 1
        Next row
        For row = 1 To 7
            grupo(i) = Cells(row, 3).Value
            i = i + 1
        Next row
        For row = 1 To 7
            grupo(i) = Cells(row, 1).Value
            i = i + 1
        Next row
    End If
    i = 1
    For row = 1 To 7
        For col = 1 To 3
            Cells(row, col).Value = grupo(i)
            i = i + 1
        Next col
    Next row
    vez = vez + 1
End If
End Sub

Now it works as intended.

Answer 3

You are adding a Cell to the Collection, but I think you really want to add the Cell's Value (so that it rewrites using the value that was in the cell when you added it to the collection, rather than writing out the value that is in the cell when you are writing it to the new location).

Option Explicit
Public vez As Long
Public grupo As New Collection

Sub magic(coluna As Long)
    Dim row As Long
    Dim col As Long
    Dim i As Long
    Set grupo = Nothing
    If vez < 3 Then
        If coluna = 1 Then
            For row = 1 To 7
                grupo.Add Cells(row, 2).Value
            Next row
            For row = 1 To 7
                grupo.Add Cells(row, 1).Value
            Next row
            For row = 1 To 7
                grupo.Add Cells(row, 3).Value
            Next row
        ElseIf coluna = 2 Then
            For row = 1 To 7
                grupo.Add Cells(row, 1).Value
            Next row
            For row = 1 To 7
                grupo.Add Cells(row, 2).Value
            Next row
            For row = 1 To 7
                grupo.Add Cells(row, 3).Value
            Next row
        ElseIf coluna = 3 Then
            For row = 1 To 7
                grupo.Add Cells(row, 2).Value
            Next row
            For row = 1 To 7
                grupo.Add Cells(row, 3).Value
            Next row
            For row = 1 To 7
                grupo.Add Cells(row, 1).Value
            Next row
        End If
        i = 1
        For row = 1 To 7
            For col = 1 To 3
                Cells(row, col).Value = grupo.Item(i)
                i = i + 1
            Next col
        Next row
        vez = vez + 1
    End If
End Sub