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
Bookmarks