Lwebzer
11-19-2019, 02:20 AM
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
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