Which system, VBA or Power Query, will function the simplest at replacing values in a worksheet. I am creating a check register. The category field is not always accurate from the bank, so I want to insert the correct category.
In VBA I have tried to use xlookup and index match. The following code is the latest iteration that I'm using.
VBA Code used:
Sub Sams()
Dim VCode As String
Dim Status As String
Dim StatusText As String
Dim ReplaceText As String
Dim EntityC As Range
Dim EntityText As String
Dim CategoryC As Range
Dim CategoryText As String
Dim DataWS As Worksheet
Dim VCodeWS As Worksheet
Dim WorkBk As Workbook
Dim LRow As Long
Dim ColumnToCheck As String
Dim XX As Long
Dim AmountT As Variant
ColumnToCheck = "A"
LastRow = Cells(Rows.Count, ColumnToCheck).End(xlUp).Row
Set WorkBk = ThisWorkbook
Set DataWS = Sheets("Data (2)")
Set VCodeWS = Sheets("V_Code")
For XX = 2 To LRow
EntityText = Range("B" & XX).Value
CategoryText = Range("D" & XX).Value
StatusText = Range("G" & XX).Value
AmountT = Range("E" & XX).Value
If EntityText = "samsclu" And StatusText = "Auto" Then Range("D" & XX).Value = "Gas"
If EntityText = "samsclu" And StatusText = "Food" Then Range("D" & XX).Value = "Groceries"
If EntityText = "samsclu" And StatusText = "Horses" Then Range("D" & XX).Value = "Fuel"
If EntityText = "samsclu" And StatusText = "USAA" Then Range("D" & XX).Value = "Groceries"
If EntityText = "samsclu" And StatusText = "Jim" Then Range("D" & XX).Value = "Groceries"
Next XX
End Sub
In power query I have used:
Power Query code:
samsclu =Table.ReplaceValue(ronaldj,
each [Category],
each if [Entity] = "samsclu" and [Status] = "Auto" then "Gas" else
if [Entity] = "samsclu" and [Status] = "Food" then "Groceries" else
if [Entity] = "samsclu" and [Status] = "USAA" then "Groceries" else
if [Entity] = "samsclu" and [Status] = "Jim" then "Groceries" else
if [Entity] = "samsclu" and [Status] = "Horses" then "Fuel" else [Category,
Replacer.ReplaceValue, {"Category"}),
End Query code.
The "Entity" variable is the code I've created for the Vendor.
The "Status" variable is the name for each checking account.
I have 140 vendors and 41 categories.
I've not been able to get the VBA to succeed and it runs slow.
The query is not consistent and is very slow, like multiminutes to complete the query.