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

[tmteast]

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

 

[Larry Haydn]

It will help if you:
* show us how your spreadsheet looks like
* what are you searching for
* what is the replacement value

 

[tmteast]

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 Account	QB Account
Advertising	60000 · Sales & Marketing:63000 · Total LMF & Marketing:63220 · Advertising
Agency Fees	60000 · Sales & Marketing:63000 · Total LMF & Marketing:63200 · Agency Fees
Bank & Finance Fees	86000 · Interest Expense:86100 · Factoring Fees
Brand Registration Fees	70000 · 300 - Sales & Adminstrative:74300 · Taxes, Licenses & Compliance
Breakage	50000 · Cost of Goods Sold:50100 · Wholesale Branded COGS
Case & Minimum Fees	60000 · Sales & Marketing:65000 · Distribution:65100 · Parkstreet
Commissions	42000 · Excise Tax & CP&I:42100 · Disc, Coupon Redemptions & Comm:42160 · Commissions
Control States Logistics	60000 · Sales & Marketing:65000 · Distribution:65210 · Control States Logistics
Consumer Research	60000 · Sales & Marketing:63000 · Total LMF & Marketing:63940 · Consumer Research
Coupon Redemptions	42000 · Excise Tax & CP&I:42100 · Disc, Coupon Redemptions & Comm:42150 · Coupon Redemptions
Custom Broker Fees	12100 · Inventory Assets:12506 · Finished Goods

Sample Expense Data:

Txn date	Invoice Number	Summary Account	Individual Account	Vendor	Memo	Brand	Amount	Column1	Column3
Txn type	Txn date	Payment date	Invoice Number	Summary Account	Individual Account	Vendor	Memo	Market	Amount
Bill	4/1/2021​		3004508​	Chargebacks	Chargebacks	Republic National Distributing Co. - MI	SAMPLES	MI	-48​
Bill	4/1/2021​		20043073​	Chargebacks	Chargebacks	Young's Market Company - CA (Tustin)	Millennium March 2021	CA	-652.8​
Credit	4/1/2021​		Amex12-20C-CR	Logistics	Shipping	Fed Ex	order#PSIEDGSO0078	CA	28.41​
Credit	4/1/2021​		Amex12-20C-CR	Logistics	Shipping	Fed Ex	order#PSIEDGSO0079	CA	29.3​
Credit	4/1/2021​		Amex12-20C-CR	Logistics	Shipping	Fed Ex	order#PSIESD21804	CA	27.65​
Credit	4/1/2021​		Amex12-20C-CR	Logistics	Shipping	Fed Ex	order#PSIESD21804	CA	16.41​
Credit	4/1/2021​		Amex12-20C-CR	Logistics	Shipping	Fed Ex	order#PSIESD21872	CA	28.63​
Credit	4/1/2021​		Amex12-20C-CR	Logistics	Shipping	Fed Ex	order#PSIESDSO0332	CA	27.43​
Credit	4/1/2021​		Amex12-20C-CR	Logistics	Shipping	Fed Ex	order#PSIESDSO0332	CA	27.43​
Credit	4/1/2021​		Amex12-20C-CR	Logistics	Shipping	Fed Ex	order#PSIESDSO0332	CA	27.43​
Bill	4/1/2021​	4/1/2021​	Amex12-20C-A	Logistics	Shipping	Fed Ex	order#PSIESD21804	CA	-27.65​

 

[Larry Haydn]

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]

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]

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.

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