Hey everyone!
For a school project I have to sort an ill-formatted table to something with more overview.
From this:
Attachment 17337
To this:
Attachment 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