Multiple Find/Replace At Once (Feeding From A Table)

[Phreaddy]

Hi,
I'm trying to adapt a macro that will allow me to find and replace multiple items in an Excel 2013 file, with a list in another Excel file.
I've found a sample of what I want to do (sorry, I can't post links yet, but see search for "Multiple Iterations of Find/Replace At Once (Feeding From A Table) (https://www.thespreadsheetguru.com/the-code-vault/2014/4/14/find-and-replace-all?rq=Multiple%20Iterations)" on thespreadsheetguru.com) but I don't know enough about Excel VBA to know what to change to fit my circumstances (for instance, I don't need to specify sheets; I do need to know where to put the name of the search/replace list, where it resides on my hard drive, etc.)
Any help would be appreciated!

[mdmackillop]

Find and replace values in Source workbook columns A & B


Sub Multi_FindReplace()
'PURPOSE: Find & Replace a list of text/values throughout entire workbook
'ORIGINAL SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
'Adapted for external data


Dim sht As Worksheet
Dim fndList As Variant
Dim x As Long
Dim Source As Workbook
Dim Target As Workbook


Set Target = ThisWorkbook
Set Source = Workbooks.Open("C:\VBAX\Source.xlsm")


fndList = Source.Sheets(1).Range("A:B").SpecialCells(2).Value
Source.Close False


'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In Target.Worksheets
sht.Cells.Replace What:=fndList(x, 1), Replacement:=fndList(x, 2), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht
Next x
End Sub

[Phreaddy]

Thanks -- That helps, I think!

[mukko]

Find and replace values in Source workbook columns A & B



Hi,

Is it somehow possible to make work little differently? That code worked for me, but I need to replace almost identical words like "glove" and "gloves" to different language "replaced_name", but running it like this it leaves the letter "s" from the gloves, and it turned out "replaced_names". Thank you, I'm new to VBA :dunno

[mdmackillop]

Change LookAt:=xlPart to LookAt:=xlWhole to replace whole words. If you need more than this, can you post a sample workbook demonstrating the issues.

[mukko]

Now I got this error "Run-time error '9': Subscript out of range" and sometimes it doesn't accept or find the source, I only got it working if I put the path as "Set Source = Workbooks.Open("example.xlsm"), it doesn't accept C:\folder\folder\example.xlsm\ even it is the same code as above in the message. I will try to upload an example file later today.

[mukko]

Ok, so here we have a file that contains repeating names of clothing "example workbook.xlsx" in 3 columns. And a file that contains terms that I wish to translate to another language (B=another) from the original (A=original). I was thinking if it would be possible also to specify the column I wish to translate, for example the column B "Clothes 2", because now it affects the whole workbook. Thank you already for the help.

[mdmackillop]

Sub Multi_FindReplace()
Dim fndList As Variant
Dim x As Long
Dim Source As Workbook
Dim Target As Range
Dim MyPath As String

MyPath = "C:\VBAX\" 'Change to suit

Set Target = ThisWorkbook.Sheets("Taul1").Columns(2)
On Error Resume Next
Set Source = Workbooks("Test_Translate.xlsm") ' If open
If Not Source Is Nothing Then
fndList = Source.Sheets(1).Range("A:B").SpecialCells(2).Value
Else
Set Source = Workbooks.Open(MyPath & "Test_Translate.xlsm") 'If closed
fndList = Source.Sheets(1).Range("A:B").SpecialCells(2).Value
Source.Close False
End If
On Error GoTo 0
'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
Target.Cells.Replace What:=fndList(x, 1), Replacement:=fndList(x, 2), _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next x
End Sub

[mukko]

Thank you so much, this works like a dream! :yes

[cgeiger]

I have a problem that is similar. I wonder if you could tell me how to tweak the code? I am not experienced at VBA - just wanna get the job done! :)

Situation: Big spreadsheet of data with ~10,000 rows and 50 columns.
For each row, I want to change the value in the field "Category" depending on the contents of the field "Product". Both are string variables. The search for contents of field "Product" is not for the whole cell.

For example, written in English, if "Product" contains the string "croissant" then change the contents of "Category" to "Pastries."

Can someone please clue me on on this one? By the way, I use both Mac and PC for these operations, depending on the day!

I would like to do this with a separate lookup table, so that future users will not need to do any VBA coding.

Many many thanks for any assistance you can provide...it's getting late!

-Chris