My first excel VBA project

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:

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

@ mana,

See PM in "Notifications" at the top of the page.

thank you so much.
but I can't understand what happens.

A more 'Basic' approach

Code:

---

PAul, it's a homework assignent

Sam

---

Simone,

You can Step thru the lngRow loop by 3s with
For = to Step 3

Then you can use Array(lngRow +1 or +2) to Return each subsequent Row

To Return a single "column" from a "Row" in an Array
Array(lngRow,ColNum)

To loop thru the 'Columns' in an Array 'Row'
For lngCol = Lbound(Array(lngRow [+n]) + 1 to Ubound(Array(lngRow [+n])
+1 skips first 'Column'

To iterate thru the Worksheet Rows and Columns
For each (step by 3) lngRow, increment lngCurrentRow by 1
For each lngCurrentRow, set lngCurrentColumn = 1
For each Cell Value assignment, increment lngCurrentColumn by 1

Thank you so much! I will try to use your suggestion and make a working code. VBA is all very new, I will try to make the loop working!