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)" 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

[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!

[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