mercredi 19 mai 2021

Nested For Next Loop with embedded If then elseif statements not picking up values. Cells in worksheet remain empty

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