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