My first excel VBA project

[mswinnie]

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