CODEWITHSUNDEEP
excelvba
fi12
fi12

Reputation: 485

Using a for loop to split up a vertical list into separate columns

I am trying to split up a vertical list of 64 numerical values into 8 separate columns of 8 values each using a for loop, but it's not working. Here is my code:

Sub Macro1()

Dim row As Integer, col As Integer

col = 65 'column 65 is where all 64 values are located

For row = 2 To 9
        Cells(row, 2).Value = Cells(row, col).Value
Next row

For row = 10 To 17
        Cells(row - 8, 3).Value = Cells(row, col).Value
Next row

For row = 18 To 25
        Cells(row - 16, 4).Value = Cells(row, col).Value
Next row

For row = 26 To 33
        Cells(row - 24, 5).Value = Cells(row, col).Value
Next row

For row = 34 To 41
        Cells(row - 32, 6).Value = Cells(row, col).Value
Next row

For row = 42 To 49
        Cells(row - 40, 7).Value = Cells(row, col).Value
Next row

For row = 50 To 57
        Cells(row - 48, 8).Value = Cells(row, col).Value
Next row

For row = 58 To 65
        Cells(row - 56, 9).Value = Cells(row, col).Value
Next row

End Sub

What's the problem here? When I run this, no new columns are created and the original list of 64 values remains unchanged.

Upvotes: 0

Views: 107

Answers (2)

AAA
AAA

Reputation: 3670

Sub Macro1()
Dim row As Integer, col As Integer, i As Integer

With ThisWorkbook.Sheets("Sheet1") 'Change to your sheet name.
    col = .Range("BN2").Column 'Change to your column letters as required.
    For row = 2 To 9
        i = (row - 2) * 8 + 2
        .Range(.Cells(2, row), .Cells(9, row)).Value = .Range(.Cells(i, col), .Cells(i + 8, col)).Value
    Next row
End With

End Sub

Upvotes: 2

Justyna MK
Justyna MK

Reputation: 3563

As discussed in the commentary section, the solution is for the "source" data to be in cells BM2:BM64 (column reference = 65).

Upvotes: 1

Related Questions