mswinnie
10-16-2016, 04:42 AM
Hey everyone!
For a school project I have to sort an ill-formatted table to something with more overview.
From this:
17337
To this:
17338
So far I was only able to find and locate the city name :( using the following code:
Sub subTransformTable()
Dim arrTableTransform() As Variant
'1) Bring the value of the table and to an array variable
arrTableTransform = Range("TableTransform").Value
Dim strCity As Stringstr
City = "City"
Dim strMonth As Stringstr
Month = "Month"
Dim lngProfit As Long'
lngProfit = "Profit"
Dim lngYear As Long'
lngYear = "Year"
Dim lngCurrent
Row As Long
Dim lngRow As Long
'Calculate the dimension of the target table (column 2, same row)
'loop through that array
'if you find city then: add the city in range ("H" and lngCurentRow)
'add the month in range ("I" and lngCurrentRow)
'add the profit in range ("J" & lngCurrentrow)
'add the year in range ("K" & lngCurrenrow
'next lngCurrentRow = 1
For lngRow = LBound(arrTableTransform, 1) To UBound(arrTableTransform, 1)
If arrTableTransform(lngRow, 1) = strCity Then
Range("H" & lngCurrentRow).Value = arrTableTransform(lngRow, 2)
lngCurrentRow = lngCurrentRow + 1
End If
Next
End Sub
I'm very new to VBA and I am super unsure what to do to create a code to format the data better. Is there someone on the forum who has some advice on how to sort the data to the example?
Super big thank you!
Simone
For a school project I have to sort an ill-formatted table to something with more overview.
From this:
17337
To this:
17338
So far I was only able to find and locate the city name :( using the following code:
Sub subTransformTable()
Dim arrTableTransform() As Variant
'1) Bring the value of the table and to an array variable
arrTableTransform = Range("TableTransform").Value
Dim strCity As Stringstr
City = "City"
Dim strMonth As Stringstr
Month = "Month"
Dim lngProfit As Long'
lngProfit = "Profit"
Dim lngYear As Long'
lngYear = "Year"
Dim lngCurrent
Row As Long
Dim lngRow As Long
'Calculate the dimension of the target table (column 2, same row)
'loop through that array
'if you find city then: add the city in range ("H" and lngCurentRow)
'add the month in range ("I" and lngCurrentRow)
'add the profit in range ("J" & lngCurrentrow)
'add the year in range ("K" & lngCurrenrow
'next lngCurrentRow = 1
For lngRow = LBound(arrTableTransform, 1) To UBound(arrTableTransform, 1)
If arrTableTransform(lngRow, 1) = strCity Then
Range("H" & lngCurrentRow).Value = arrTableTransform(lngRow, 2)
lngCurrentRow = lngCurrentRow + 1
End If
Next
End Sub
I'm very new to VBA and I am super unsure what to do to create a code to format the data better. Is there someone on the forum who has some advice on how to sort the data to the example?
Super big thank you!
Simone