Single column into matrix

[JoaoNorris]

Hello everyone,

I recently started working with VBA and I'm facing a problem with turning a single column into a matrix.
So, I have data in steps of 15 minutes for an entire month (4x24(hours)x31(days) = 2 976‬) resulting in a column of 2976 inputs. What I have to do is getting columns of 96 rows (equals each day), resulting in a matrix with 96 rows and 31 columns.

I tried doing a for loop but couldn't make it work since i didn't know how to start the next iteration on the 97th row, to place it on the second column and so on.

I know this is really basic, but if anyone could help I would be very thankfull,

Best regards!

[大灰狼1976]

Hi JoaoNorris
A simple example

Sub test()
Dim tm, rng As Range, rngs As Range, r&
r = Cells(Rows.Count, 1).End(3).Row
If r > 1 Then r = -Int(-r / 96) * 96 + 1
Set rngs = Cells(r, 1).Resize(96, 31)
tm = #2/1/2020#
For Each rng In rngs
  rng = Format(tm, "yyyy/m/d hh:mm")
  tm = tm + TimeSerial(0, 15, 0)
  If Month(tm) > 2 Then Exit For
Next rng
End Sub

[Leith Ross]

Hello JoaoNorris,

If your data is already in a single column then you can use this macro to create the 2-D matrix. The code is set to look at "A1" on "Sheet1". You can change this to what you need.

Sub TimeMatrix()


    Dim c       As Long
    Dim Cell    As Range
    Dim k       As Long
    Dim n       As Long
    Dim vaOut() As Variant
    Dim r       As Long
    Dim Wks     As Worksheet
    
        Set Wks = ThisWorkbook.Worksheets("Sheet1")
        Set Cell = Wks.Range("A1")
        
        n = 24 * 31 * 4
        ReDim vaOut(30, 95)
        
            For k = 0 To n - 1
                r = k \ 96
                c = k Mod 96
                vaOut(r, c) = Cell.Offset(k, 0)
            Next k
            
        Cell.Resize(k, 1).Value = ""
        Cell.Resize(31, 96).Value = vaOut
            
End Sub