Data Conversion using Dynamic Multi-Dimensional Array

[Lwebzer]

Hi everyone,

I'm trying to write a macro that will help me convert datasets to flat formats in a dynamic and scalable way. I'm doing it partly because it's useful for me and partly because I want to learn VBA (I'm a beginner).

I would greatly appreciate any tips on why the below code doesn't seem to be working. It tells me "Compile Error: Invalid Next control variable Reference". In bold and underlined is where I think the problem is.


Thank you!


_______________

Here is an example of the kind of data that I would want to convert. I know it's not complete (e.g., the headers aren't going to get converted atm, but this has become a roadblock unfortunately).

Dataset example (pre-conversion)





Metric
Bookings
Bookings
4 Wall
4 Wall


Date
7/1/2014
8/1/2014
11/1/2014
12/1/2014


1052
$ 38K
$ 31K
$ 120K
$ 29K


1105
$ -
$ -
$ 9K
$ 0K


1110
$ 25K
$ 56K
$ 37K
$ 35K


1123
$ -
$ 25K
$ -
$ 21K




Dataset example (post conversion)





Customer ID
Metric
Period
Value


1052
Bookings
7/1/2014
$ 38K


1052
Bookings
8/1/2014
$ 31K


1052
4 Wall
11/1/2014
$ 120K


1052
4 Wall
12/1/2014
$ 29K


1105
Bookings
7/1/2014
$ -


1105
Bookings
8/1/2014
$ -


1105
4 Wall
11/1/2014
$ 9K


1105
4 Wall
12/1/2014
$ 0K


1110
Bookings
7/1/2014
$ 25K


1110
Bookings
8/1/2014
$ 56K


1110
4 Wall
11/1/2014
$ 37K


1110
4 Wall
12/1/2014
$ 35K


1123
Bookings
7/1/2014
$ -


1123
Bookings
8/1/2014
$ 25K


1123
4 Wall
11/1/2014
$ -


1123
4 Wall
12/1/2014
$ 21K



Code


Sub LG_Data_Converter()




Dim Nmbr_Headers As Byte
Nmbr_Headers = Application.InputBox("Input Required", "How many Header Rows are there?", Type:=1, Default:=2)


Dim FirstRow As Long, LastRow As Long, FirstColumn As Long, LastColumn As Long

FirstRow = Cells.Find(What:="*", After:=Range("XFD300000"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row


LastRow = Cells.Find(What:="*", After:=Range("XFD300000"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

FirstColumn = Cells.Find(What:="*", After:=Range("XFD300000"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

LastColumn = Cells.Find(What:="*", After:=Range("XFD300000"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Dim No_Data_Rows As Long
No_Data_Rows = LastRow - FirstRow - Nmbr_Headers + 1 '(Inclusive)'

Dim No_Data_Columns As Long
No_Data_Columns = LastColumn - FirstColumn + 1 - 1 '(Take into account the customer ID column)'

Dim Dataset() As Variant
ReDim Dataset(1 To No_Data_Rows, 1 To No_Data_Columns)

Dim i As Long, j As Long

For i = 1 To No_Data_Rows
For j = 1 To No_Data_Columns

Dataset(i, j) = Cells(i, j)

Next i
Next j



End Sub

[Jan Karel Pieterse]

You've got the two Next's reversed in order.
NB: PowerQuery can do this easily, no coding involved.

[paulked]

Jan, TS doesn't want PQ
partly because I want to learn VBA ;)

[Lwebzer]

thank you! Why is it that they need to be reversed in order? I’m just trying to learn some of the basic foundations of vba and also power query is less scalable I think, easier to roll out to other team members if they just need to click a button (?)

thank you again! I’ll try it again tomorrow Am, really hope it works...

also, do I not need to use ubound? I tried to get round it by using last row / last column, not sure if that’s required.

thank you again.:)


You've got the two Next's reversed in order.
NB: PowerQuery can do this easily, no coding involved.

[Jan Karel Pieterse]

PQ is certainly not less scalable. And it is easier to learn than VBA.
WHen you are nesting for-next loops, the inner For-Next combination belongs to each other and the outer combination. SO if you start with a for j =, then you must also end with a next j

[snb]

To unpivot the pivot:


Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion
ReDim sp((UBound(sn) - 2) * (UBound(sn, 2) - 1), 3)

For j = 0 To UBound(sp) - 1
y= UBound(sn, 2) - 1
x = j \ y + 3
y = j Mod y + 2
sp(j, 0) = sn(x, 1)
sp(j, 1) = sn(1, y)
sp(j, 2) = sn(2, y)
sp(j, 3) = sn(x, y)
Next

Cells(1, 8).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
End Sub

[Lwebzer]

Thanks snb! :) This is great! Trying to build with my own code but this is great to refer to.

[Lwebzer]

Aha! Thank you. :) I will be sure to also check out PowerQuery.

Appreciate all the help.

[snb]

Alternative:


Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion

With CreateObject("scripting.dictionary")
For j = 0 To (UBound(sn) - 2) * (UBound(sn, 2) - 1) - 1
y = UBound(sn, 2) - 1
x = j \ y + 3
y = j Mod y + 2
.Item(.Count) = Array(sn(x, 1), sn(1, y), CLng(sn(2, y)), sn(x, y))
Next

Cells(1, 12).Resize(.Count, 4) = Application.Index(.items, 0, 0)
End With
End Sub

[p45cal]

You really should supply a workbook with all this in - it will answer some questions rather than have us guess (wrongly) what's there and save us duplicating your setup.
What you're trying to do is unpivot your data table. Sometimes you can use a pivot table (paradoxically!), nowadays more often Power Query, otherwise it's a case of using vba to run through each cell in the databody of your table, and if it's not a blank cell, picking up the the 1 or 2 headers above each cell and the row header to the left, as well as the value of the cell itself.
Do supply a workbook.

[Lwebzer]

25452

Hi Pascal,

Thanks for the feedback. Attaching a workbook here (hoping this attachment got uploaded properly) I do really really appreciate your all's help and don't ever want you to use your time inefficiently. I already feel a bit weird using so much of someone else's time without any compensation.

I know that I could in theory use Power Query / Pivot Tables to do this task. The reasons I'm trying to get to the bottom of it using VBA are:

1) If others were to use this template it might be better if they could just do it with a click of a button rather than going through Power QUery / Pivot Tables
2) I'm trying to improve my skills in VBA first, with a view to then potentially moving on to R / Python. I'm pretty much a beginner in all this now but want to get to a place where I can leverage large amounts of data to do analysis more quickly and effectively. My sense is that arrays are a pretty fundamental concept to VBA and a lot of other coding languages.

I really appreciate your support both on this specific task as well as on any continued feedback about how best to leverage this forum and / or how to continue to progress my learnings.

Thank you for all your help,

Louis

[snb]

Why do you ignore contributors to this thread ?

[Lwebzer]

Hey snb, I know I have two threads on this and I should have probably kept it as one. My bad.

Not trying to ignore any contributors - please know that I've spent a lot of time trying to understand every response that has been graciously submitted. When a response only involves code, but no explanations, it is difficult for me to follow what's going on. This might explain why. I'm just not good enough with VBA.

Hopefully that provides an adequate answer. I do really appreciate your responses and think I had replied to your responses on my other thread.

Thank you again.