PDA

View Full Version : [SOLVED] My first excel VBA project



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

SamT
10-16-2016, 06:34 AM
@ mana,

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

mana
10-16-2016, 06:53 AM
thank you so much.
but I can't understand what happens.

Paul_Hossler
10-16-2016, 06:59 AM
A more 'Basic' approach




PAul, it's a homework assignent

Sam

SamT
10-16-2016, 07:13 AM
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

mswinnie
10-16-2016, 07:29 AM
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!