Excel/VBA - Find/Replace one column based on Specific Table

tmteast

New Member
Joined
Apr 12, 2021
Messages
7
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
Hello, I am working on a Macro and I am very close. When the Find/Replace happens it is replacing more than I want.

I am not a coder and would like help finishing this script I have borrowed from thespreadsheetguru.

When the find/replace happens I want it to only Search in one Column G:G in the Current Sheet. I have added what I thought would work in a few places but cannot seem to keep it bound to the one column.

Thanks in advance.

Sub AccountsPStoQB()
'
' AccountsPStoQB Macro
'

'

'PURPOSE: Find & Replace a list of text/values throughout entire workbook from a table
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant


'Create variable to point to your table
Set tbl = Worksheets("Accounts").ListObjects("Table7")

'Create an Array out of the Table's Data
Set TempArray = tbl.DataBodyRange
myArray = Application.Transpose(TempArray)

'Designate Columns for Find/Replace data
fndList = 1
rplcList = 2

'Loop through each item in Array lists
For x = LBound(myArray, 1) To UBound(myArray, 2)
'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
For Each sht In ActiveWorkbook.Worksheets
If sht.Name <> tbl.Parent.Name Then

sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

End If
Next sht
Next x


End Sub
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
137
Office Version
  1. 365
Platform
  1. Windows
It will help if you:
* show us how your spreadsheet looks like
* what are you searching for
* what is the replacement value
 

tmteast

New Member
Joined
Apr 12, 2021
Messages
7
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
It will help if you:
* show us how your spreadsheet looks like
* what are you searching for
* what is the replacement value
No problem, I have attached some sample data below.

The find/replace is using the Table on the Accounts Sheet as a dynamic reference table. It gives us the ability to add new accounts later and not have to reprogram the macro. Anything in the first column is what it is searching for. The second column is used as replace data. In this case we want to only search "Individual Account" Column and not the other columns. As it is replacing words like shipping and commissions in columns that should not be changed.

Really, thank you for the help here.

Sample Accounts Table:
PS Individual AccountQB Account
Advertising60000 · Sales & Marketing:63000 · Total LMF & Marketing:63220 · Advertising
Agency Fees60000 · Sales & Marketing:63000 · Total LMF & Marketing:63200 · Agency Fees
Bank & Finance Fees86000 · Interest Expense:86100 · Factoring Fees
Brand Registration Fees70000 · 300 - Sales & Adminstrative:74300 · Taxes, Licenses & Compliance
Breakage50000 · Cost of Goods Sold:50100 · Wholesale Branded COGS
Case & Minimum Fees60000 · Sales & Marketing:65000 · Distribution:65100 · Parkstreet
Commissions42000 · Excise Tax & CP&I:42100 · Disc, Coupon Redemptions & Comm:42160 · Commissions
Control States Logistics60000 · Sales & Marketing:65000 · Distribution:65210 · Control States Logistics
Consumer Research60000 · Sales & Marketing:63000 · Total LMF & Marketing:63940 · Consumer Research
Coupon Redemptions42000 · Excise Tax & CP&I:42100 · Disc, Coupon Redemptions & Comm:42150 · Coupon Redemptions
Custom Broker Fees12100 · Inventory Assets:12506 · Finished Goods

Sample Expense Data:
Txn dateInvoice NumberSummary AccountIndividual AccountVendorMemoBrandAmountColumn1Column3
Txn typeTxn datePayment dateInvoice NumberSummary AccountIndividual AccountVendorMemoMarketAmount
Bill
4/1/2021​
3004508​
ChargebacksChargebacksRepublic National Distributing Co. - MISAMPLESMI
-48​
Bill
4/1/2021​
20043073​
ChargebacksChargebacksYoung's Market Company - CA (Tustin)Millennium March 2021CA
-652.8​
Credit
4/1/2021​
Amex12-20C-CRLogisticsShippingFed Exorder#PSIEDGSO0078CA
28.41​
Credit
4/1/2021​
Amex12-20C-CRLogisticsShippingFed Exorder#PSIEDGSO0079CA
29.3​
Credit
4/1/2021​
Amex12-20C-CRLogisticsShippingFed Exorder#PSIESD21804CA
27.65​
Credit
4/1/2021​
Amex12-20C-CRLogisticsShippingFed Exorder#PSIESD21804CA
16.41​
Credit
4/1/2021​
Amex12-20C-CRLogisticsShippingFed Exorder#PSIESD21872CA
28.63​
Credit
4/1/2021​
Amex12-20C-CRLogisticsShippingFed Exorder#PSIESDSO0332CA
27.43​
Credit
4/1/2021​
Amex12-20C-CRLogisticsShippingFed Exorder#PSIESDSO0332CA
27.43​
Credit
4/1/2021​
Amex12-20C-CRLogisticsShippingFed Exorder#PSIESDSO0332CA
27.43​
Bill
4/1/2021​
4/1/2021​
Amex12-20C-ALogisticsShippingFed Exorder#PSIESD21804CA
-27.65​
 

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
137
Office Version
  1. 365
Platform
  1. Windows
You wish to search within the [Individual Account] column...
...that is the column that has "Chargebacks", "Shipping" etc.?

And you will be matching the searched item against the Sample Account Table, column [PS Individual Accounts]?

And you want to replace (overwrite) the item found with the QB Account?
Or do you want to write it into another column?
 

tmteast

New Member
Joined
Apr 12, 2021
Messages
7
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
You wish to search within the [Individual Account] column...
...that is the column that has "Chargebacks", "Shipping" etc.?

And you will be matching the searched item against the Sample Account Table, column [PS Individual Accounts]?

And you want to replace (overwrite) the item found with the QB Account?
Or do you want to write it into another column?
That is all correct, I would like to replace (overwrite) the with the item found in the QB Account.

Thanks
 

tmteast

New Member
Joined
Apr 12, 2021
Messages
7
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
I was able to figure it out. Through trial and error I was able to select the column only instead of the entire sheet by replacing sht.Cells.Replace to Columns("G").Cells.Replace. Works great. Thanks again for the help. In case anyone else needs it, here is the full script.





VBA Code:
Sub AccountsPStoQB()
'
' AccountsPStoQB Macro
'

'
    
    'PURPOSE: Find & Replace a list of text/values throughout entire workbook from a table
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant


'Create variable to point to your table
  Set tbl = Worksheets("Accounts").ListObjects("Table7")

'Create an Array out of the Table's Data
  Set TempArray = tbl.DataBodyRange
  myArray = Application.Transpose(TempArray)
  
'Designate Columns for Find/Replace data
  fndList = 1
  rplcList = 2

'Loop through each item in Array lists
  For x = LBound(myArray, 1) To UBound(myArray, 2)
    'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
      For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> tbl.Parent.Name Then
          
         
          Columns("G").Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
        
        End If
      Next sht
  Next x


End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,132,642
Messages
5,654,547
Members
418,139
Latest member
nimesh72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top