Need help extending coding

[JAdrian13]

The following is my current code and works properly. I would like to add to it to where it will choose the date from column "A" and calculate the same information (Sum for each category) and do so for each month of the year. The final output will give me an amount spent in each category for each month. Any advise would be great.


The excel sheet used looks like this:

A1 B1 C1
Date Category Cost

3 Jan 2013 Food $50.67
20 Jan 2013 Athletics $100.00
3 Feb 2013 Restaurant/Bar $45.67
12 Feb 2013 Food $9.73
etc etc etc



Option Explicit

'==========================================Specify Variables====================================================='

Sub SortByCategory()

Dim ws As Worksheet
Dim lRow As Long

Dim i As Integer 'Row counter
Dim SumGroceries As Integer 'Category counter for sum
Dim SumLiving As Integer
Dim SumRestaurantBar As Integer
Dim SumAthletics As Integer
Dim Cat As String 'Category string for sorting


'========================================== Specify Worksheet===================================================='

Set ws = ThisWorkbook.Sheets("2013") 'Need to change the worksheet name as years are added
With ws
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With


'==========================================Sum Totals Per Category==============================================='


For i = 2 To lRow
Cat = ws.Range("B" & i)


'Input each of the catgories listed in the "Categories" tab below (ensure the SumCategory is also added to variable list As Integer)

If Cat = "Groceries" Then SumGroceries = SumGroceries + ws.Range("C" & i)
If Cat = "Living" Then SumLiving = SumLiving + ws.Range("C" & i)
If Cat = "Restaurant/Bar" Then SumRestaurantBar = SumRestaurantBar + ws.Range("C" & i)
If Cat = "Athletics" Then SumAthletics = SumAthletics + ws.Range("C" & i)

Next i


'==========================================Message box to test coding=============================================='


MsgBox "Groceries " & SumGroceries & "Living " & SumLiving & "Rest/Bar " & SumRestaurantBar & "Athletics " & SumAthletics


End Sub

[Norie]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[ragulduy]

Something like:

Option Explicit



'==========================================Specify Variables====================================================='

Sub SortByCategory()

Dim ws As Worksheet
Dim lRow As Long

Dim i As Integer 'Row counter
Dim SumGroceries As Integer 'Category counter for sum
Dim SumLiving As Integer
Dim SumRestaurantBar As Integer
Dim SumAthletics As Integer
Dim Cat As String 'Category string for sorting
Dim month_count


'========================================== Specify Worksheet===================================================='

Set ws = ThisWorkbook.Sheets("2013") 'Need to change the worksheet name as years are added
With ws
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With


'==========================================Sum Totals Per Category==============================================='

For month_count = 1 To 12
    For i = 2 To lRow
    Cat = ws.Range("B" & i)
    
    
    'Input each of the catgories listed in the "Categories" tab below (ensure the SumCategory is also added to variable list As Integer)
    
    If Cat = "Groceries" And month_count = Month(Range("A" & i)) Then SumGroceries = SumGroceries + ws.Range("C" & i)
    If Cat = "Living" And month_count = Month(Range("A" & i)) Then SumLiving = SumLiving + ws.Range("C" & i)
    If Cat = "Restaurant/Bar" And month_count = Month(Range("A" & i)) Then SumRestaurantBar = SumRestaurantBar + ws.Range("C" & i)
    If Cat = "Athletics" And month_count = Month(Range("A" & i)) Then SumAthletics = SumAthletics + ws.Range("C" & i)
    
    Next i
    MsgBox "Month: " & month_count & " Groceries " & SumGroceries & "Living " & SumLiving & "Rest/Bar " & SumRestaurantBar & "Athletics " & SumAthletics
Next


'==========================================Message box to test coding=============================================='


MsgBox "Groceries " & SumGroceries & "Living " & SumLiving & "Rest/Bar " & SumRestaurantBar & "Athletics " & SumAthletics


End Sub

Assuming your dates are actually dates and not just text strings.