#excel #vba #for-loop #if-statement #nested
Вопрос:
Я работаю с двумя книгами — в первой есть часть каждой строки выписки по моей кредитной карте и соответствующая категория, в то время как другая книга загружается непосредственно с веб-сайта моей кредитной карты. Моя цель состоит в том, чтобы определить, к какой категории относится каждая строка отчета, а затем обобщить информацию в первой рабочей книге.
Код выполняется без каких-либо ошибок, но таблица, в которой должна быть вся информация, остается пустой. Когда я заменяю, например, переменную, которая должна принимать значение «электричество», на «тест», в моей первой рабочей книге появляется слово «тест». Я подозреваю, что где-то допустил ошибку в вложенном для следующего раздела моего кода, но не могу определить, что это такое. Спасибо, что позволили мне поделиться!
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:UsersMyNameDownloads" amp; Company amp; " Statements" amp; Year amp; "" amp; Month amp; ".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" amp; y).Value, Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("C" amp; x)) > 0 Then
If Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 11 Then
Clothes = Clothes Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 11 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 15 Then
Electronics = Electronics Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 15 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 18 Then
Online_Shopping = Online_Shopping Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 18 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 28 Then
Other = Other Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 28 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 35 Then
Vaping_Products = Vaping_Products Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 35 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 41 Then
Flight_Travel = Flight_Travel Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 41 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 42 Then
Gas = Gas Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 42 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 43 Then
Vehicle_Payments = Vehicle_Payments Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 43 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 44 Then
Cable_Internet = Cable_Internet Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 44 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 45 Then
Car_Insurance = Car_Insurance Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 45 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 46 Then
Electricity = Electricity Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 46 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 50 Then
Liquor = Liquor Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 50 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 75 Then
Restaurants = Restaurants Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 75 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 76 Then
Bloomberg = Bloomberg Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 76 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 77 Then
Gym = Gym Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 77 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 78 Then
Netflix = Netflix Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 78 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 79 Then
Netflix = Netflix Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 79 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 80 Then
Prime_Video = Prime_Video Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 80 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 81 Then
Seeking_Alpha = Seeking_Alpha Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 81 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 82 Then
Spotify = Spotify Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 82 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 83 Then
WSJ = WSJ Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 83 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 84 Then
Hannahford = Hannahford Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 84 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 85 Then
Market_Basket = Market_Basket Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 85 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 86 Then
Trader_Joes = Trader_Joes Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 86 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 87 Then
Vitamin_Shoppe = Vitamin_Shoppe Range("D" amp; y).Value
ElseIf Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value > 87 And Workbooks("Personal Financial Dashboard.xlsm").Sheets("Categories").Range("E" amp; x).Value <= 88 Then
Wholesale_Clubs = Wholesale_Clubs Range("D" amp; 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
Ответ №1:
Ваш код работает для меня, поэтому я подозреваю, что у вас могут быть скрытые пробелы в исходных данных. Используйте функцию TRIM (), чтобы удалить их. Попробуй
Option Explicit
Sub Open_And_Transfer_Statement()
'Determine User-Identified Statement and Open Corresponding Workbook
Dim wb As Workbook, wsCateg As Worksheet, wsBudget As Worksheet
Dim sCateg As String, s As String, n As Integer, a, amount As Currency
Dim t0 As Single: t0 = Timer
' dashboard
Set wb = Workbooks("Personal Financial Dashboard.xlsm")
Set wsCateg = wb.Sheets("Categories")
Set wsBudget = wb.Sheets("Comprehensive_Monthly_Budget")
' statement
Dim wbSrc As Workbook, wsSrc As Worksheet
Dim Month As Integer, Year, Company As String, sFilename As String
With wsBudget
Month = Range("L3")
Year = Range("M3")
Company = Range("N3")
End With
sFilename = "C:UsersMyNameDownloads" amp; Company amp; " Statements" amp; Year amp; "" amp; Month amp; ".xls"
Set wbSrc = Workbooks.Open(sFilename, , 1) ' read only
Set wsSrc = wbSrc.Sheets(1) ' January
'Begin Transfering Each Statement Item to Corresponding Category in Dashboard
Dim yLastRow As Long, y As Long
Dim xLastRow As Long, x As Long
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
' copy categories into array
Dim arCateg
xLastRow = wsCateg.Cells(Rows.Count, "C").End(xlUp).Row
arCateg = wsCateg.Range("C1:E" amp; xLastRow).Value2
' scan statement
yLastRow = wsSrc.Cells(Rows.Count, "C").End(xlUp).Row
For y = 14 To yLastRow ' statement
s = Trim(wsSrc.Cells(y, "C"))
amount = wsSrc.Cells(y, "D")
For x = 2 To xLastRow ' categ
sCateg = trim(arCateg(x, 1)) ' col C
n = arCateg(x, 3) ' col E
If InStr(s, sCateg) > 0 Then
If n <= 11 Then
Clothes = Clothes amount
ElseIf n > 11 And n <= 15 Then
Electronics = Electronics amount
ElseIf n > 15 And n <= 18 Then
Online_Shopping = Online_Shopping amount
ElseIf n > 18 And n <= 28 Then
Other = Other amount
ElseIf n > 28 And n <= 35 Then
Vaping_Products = Vaping_Products amount
ElseIf n > 35 And n <= 41 Then
Flight_Travel = Flight_Travel amount
ElseIf n > 41 And n <= 42 Then
Gas = Gas amount
ElseIf n > 42 And n <= 43 Then
Vehicle_Payments = Vehicle_Payments amount
ElseIf n > 43 And n <= 44 Then
Cable_Internet = Cable_Internet amount
ElseIf n > 44 And n <= 45 Then
Car_Insurance = Car_Insurance amount
ElseIf n > 45 And n <= 46 Then
Electricity = Electricity amount
ElseIf n > 46 And n <= 50 Then
Liquor = Liquor amount
ElseIf n > 50 And n <= 75 Then
Restaurants = Restaurants amount
ElseIf n > 75 And n <= 76 Then
Bloomberg = Bloomberg amount
ElseIf n > 76 And n <= 77 Then
Gym = Gym amount
ElseIf n > 77 And n <= 78 Then
Netflix = Netflix amount
ElseIf n > 78 And n <= 79 Then
Netflix = Netflix amount
ElseIf n > 79 And n <= 80 Then
Prime_Video = Prime_Video amount
ElseIf n > 80 And n <= 81 Then
Seeking_Alpha = Seeking_Alpha amount
ElseIf n > 81 And n <= 82 Then
Spotify = Spotify amount
ElseIf n > 82 And n <= 83 Then
WSJ = WSJ amount
ElseIf n > 83 And n <= 84 Then
Hannahford = Hannahford amount
ElseIf n > 84 And n <= 85 Then
Market_Basket = Market_Basket amount
ElseIf n > 85 And n <= 86 Then
Trader_Joes = Trader_Joes amount
ElseIf n > 86 And n <= 87 Then
Vitamin_Shoppe = Vitamin_Shoppe amount
ElseIf n > 87 And n <= 88 Then
Wholesale_Clubs = Wholesale_Clubs amount
End If
End If
Next x
Next y
With wsBudget
.Activate
.Range("D10") = Electricity
.Range("D11") = Cable_Internet
.Range("D12") = Housing_Subtotal
.Range("D15") = Vehicle_Payments
.Range("D16") = Car_Insurance
.Range("D17") = Gas
.Range("D18") = Flight_Travel
.Range("D19") = Transportation_Subtotal
.Range("D22") = Market_Basket
.Range("D23") = Hannahford
.Range("D24") = Trader_Joes
.Range("D25") = Wholesale_Clubs
.Range("D26") = Groceries_Subtotal
.Range("D29") = Netflix
.Range("D30") = Gym
.Range("D31") = Spotify
.Range("D32") = Bloomberg
.Range("D33") = WSJ
.Range("D34") = Prime_Video
.Range("D35") = Subscriptions_Subtotal
.Range("I8") = Restaurants
.Range("I9") = Liquor
.Range("I10") = Dining_Out_Subtotal
.Range("I13") = Clothes
.Range("I14") = Online_Shopping
.Range("I15") = Electronics
.Range("I16") = Vaping_Products
.Range("I17") = Other
.Range("I18") = Merchandise_Subtotal
End With
wbSrc.Close False
MsgBox "Done in " amp; Format(Timer - t0, "0.00 seconds")
End Sub