How can display multiple values to single column?

Vba - how to count several values in xls and display a result

• QUESTION: I would like to ask you for help. I am a VBA beginner and try to load several values in xls colmumn into variable, then to display a result. Within my source sheet are 3 key column and a lot of rows with the following data: Col A (Year) Col B (Date) Col C (Value) 2010 1.1.2010 30? 2010 1.2.2010 3000? 2011 3.7.2011 300? 2010 5.1.2010 50? 2010 15.2.2010 100? 2011 30.7.2011 3? etc.. I would like to check every row in related column and count values with the same year/month, then display result in another cell. The result above mentioned example would be: 1 (January 2010)- 30?+50?=80? - display 1 in one cell and 80? in adjacent cell; 2 (February 2010) - 3000?+100?=3100? - display 2 in one cell and 3100? in adjacent cell; 7 (July 2011) - 300?+3?=303? - display 7 in one cell and 303? in adjacent cell etc.. Could you willing to help me please with my "homework"? Thank you very much. Best Martin ANSWER: Marek, this worked for me: I assume the data you show starts in A2 with headers in the first row. It places the results starting in E2/F2 I assume you only have data for years 2010 and 2011. You didn't say whether to keep the years separate or not - I did, but I didn't label the output by years. The first values will be 2010 and the later values will be 2011. Code can easily be added to label years. Sub ABC() Dim v2010(1 To 12) Dim v2011(1 To 12) Dim r As Range, cell As Range Dim mnth As Long Dim rw As Long, i As Long Set r = Range("B2", Cells(Rows.Count, "B").End(xlUp)) For Each cell In r mnth = Month(cell) If cell.Offset(0, -1) = 2010 Then v2010(mnth) = v2010(mnth) + cell.Offset(0, 1) ElseIf cell.Offset(0, -1) = 2011 Then v2011(mnth) = v2011(mnth) + cell.Offset(0, 1) End If Next rw = 2 For i = 1 To 12 If v2010(i) > 0 Then Cells(rw, "F").Value = v2010(i) Cells(rw, "E").Value = i rw = rw + 1 End If Next i For i = 1 To 12 If v2011(i) > 0 Then Cells(rw, "F").Value = v2011(i) Cells(rw, "E").Value = i rw = rw + 1 End If Next End Sub ---------- FOLLOW-UP ---------- QUESTION: thank you very much, you are really professional. Please, could you add a year as well? I tried to add but without success. Best Marek

• Answer:

  Marek, Sub ABC() Dim v2010(1 To 12) Dim v2011(1 To 12) Dim r As Range, cell As Range Dim mnth As Long Dim rw As Long, i As Long Set r = Range("B2", Cells(Rows.Count, "B").End(xlUp)) For Each cell In r mnth = Month(cell) If cell.Offset(0, -1) = 2010 Then v2010(mnth) = v2010(mnth) + cell.Offset(0, 1) ElseIf cell.Offset(0, -1) = 2011 Then v2011(mnth) = v2011(mnth) + cell.Offset(0, 1) End If Next rw = 2 For i = 1 To 12 If v2010(i) > 0 Then cells(rw, "D").Value = 2010 Cells(rw, "F").Value = v2010(i) Cells(rw, "E").Value = i rw = rw + 1 End If Next i For i = 1 To 12 If v2011(i) > 0 Then cells(rw, "D").Value = 2011 Cells(rw, "F").Value = v2011(i) Cells(rw, "E").Value = i rw = rw + 1 End If Next End Sub