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