I'm working with two workbooks - the first has a portion of each line item of my credit card statement and a corresponding category while the other workbook is downloaded directly from my credit card's website. My goal is to identify which category each line item on the statement belongs to and then summarize the information in the first workbook.
The code executes without any errors, but the table where all the information is supposed to be remains empty. When I replace, for example, the variable that is supposed to take the value for 'electricity' with 'test', the word 'test' appears in my first workbook. I'm suspecting that I've made a mistake somewhere in the nested for-next section of my code but am unable to identify what it is. Thank you for letting me share!
Sub Open_And_Transfer_Statement()
'Determine User-Identified Statement and Open Corresponding Workbook
Dim Month, Year, Company As String
Month = Range("L3")
Year = Range("M3")
Company = Range("N3")
Workbooks.Open Filename:="C:\Users\MyName\Downloads\" & Company & " Statements\" & Year & "\" & Month & ".xls"
'Begin Transfering Each Statement Item to Corresponding Category in Dashboard
Dim yLastRow, y As Integer
Dim xLastRow, x As Integer
Dim Clothes, Electronics, Online_Shopping, Other, Vaping_Products, Flight_Travel, Gas, Vehicle_Payments, Cable_Internet, Car_Insurance, Electricity, Liquor As Currency
Dim Restaurants, Bloomberg, Gym, Netflix, Prime_Video, Seeking_Alpha, Spotify, WSJ, Hannahford, Market_Basket, Trader_Joes, Vitamin_Shoppe, Wholesale_Clubs As Currency
Dim Housing_Subtotal, Transportation_Subtotal, Groceries_Subtotal, Subscriptions_Subtotal, Dining_Out_Subtotal, Merchandise_Subtotal, Credit_Payments_Subtotal, Income_Taxes_Subtotal, Rewards_Subtotal As Currency
yLastRow = Workbooks("January.xls").Sheets("January").Range("C10000").End(xlUp).Row
xLastRow = Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("C10000").End(xlUp).Row
For x = 2 To xLastRow
For y = 14 To yLastRow
If InStr(Range("C" & y).Value, Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("C" & x)) > 0 Then
If Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 11 Then
Clothes = Clothes + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 11 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 15 Then
Electronics = Electronics + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 15 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 18 Then
Online_Shopping = Online_Shopping + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 18 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 28 Then
Other = Other + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 28 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 35 Then
Vaping_Products = Vaping_Products + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 35 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 41 Then
Flight_Travel = Flight_Travel + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 41 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 42 Then
Gas = Gas + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 42 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 43 Then
Vehicle_Payments = Vehicle_Payments + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 43 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 44 Then
Cable_Internet = Cable_Internet + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 44 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 45 Then
Car_Insurance = Car_Insurance + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 45 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 46 Then
Electricity = Electricity + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 46 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 50 Then
Liquor = Liquor + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 50 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 75 Then
Restaurants = Restaurants + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 75 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 76 Then
Bloomberg = Bloomberg + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 76 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 77 Then
Gym = Gym + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 77 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 78 Then
Netflix = Netflix + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 78 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 79 Then
Netflix = Netflix + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 79 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 80 Then
Prime_Video = Prime_Video + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 80 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 81 Then
Seeking_Alpha = Seeking_Alpha + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 81 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 82 Then
Spotify = Spotify + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 82 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 83 Then
WSJ = WSJ + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 83 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 84 Then
Hannahford = Hannahford + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 84 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 85 Then
Market_Basket = Market_Basket + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 85 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 86 Then
Trader_Joes = Trader_Joes + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 86 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 87 Then
Vitamin_Shoppe = Vitamin_Shoppe + Range("D" & y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value > 87 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" & x).Value <= 88 Then
Wholesale_Clubs = Wholesale_Clubs + Range("D" & y).Value
End If
End If
Next y
Next x
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Activate
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D10") = Electricity
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D11") = Cable_Internet
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D12") = Housing_Subtotal
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D15") = Vehicle_Payments
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D16") = Car_Insurance
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D17") = Gas
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D18") = Flight_Travel
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D19") = Transportation_Subtotal
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D22") = Market_Basket
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D23") = Hannahford
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D24") = Trader_Joes
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D25") = Wholesale_Clubs
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D26") = Groceries_Subtotal
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D29") = Netflix
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D30") = Gym
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D31") = Spotify
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D32") = Bloomberg
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D33") = WSJ
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D34") = Prime_Video
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("D35") = Subscriptions_Subtotal
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("I8") = Restaurants
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("I9") = Liquor
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("I10") = Dining_Out_Subtotal
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("I13") = Clothes
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("I14") = Online_Shopping
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("I15") = Electronics
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("I16") = Vaping_Products
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("I17") = Other
Workbooks("Personal Financial Dashboard.xlsm").Sheets("Comprehensive_Monthly_Budget").Range("I18") = Merchandise_Subtotal
Workbooks("January.xls").Close
End Sub
Aucun commentaire:
Enregistrer un commentaire