Multiple Search and Find Problem

PetraK

New Member
Joined
Jun 12, 2018
Messages
6
Hi there,

I'm struggling with a multiple search and find. I have 26 separate worksheets that are all in different languages. I am manually putting them into one worksheet (not sure if that's the fastest but I'm a newbie to all this). And then I need to get them all to be in English.

So... for every occurrence of (for example) Lundi, Kedd, Lunes etc etc. I need to change them to Monday.

Or for example people have typed a country name but in many different languages or iterations. For example I need to change USA, U.S.A, US, Amerika to "United States".

I can put the needed information into two columns (e.g. Lundi in A1, Monday in B1, Kedd in A2, Monday in B2... and so on) or into whatever format works best to enable the changes.

I have over 22,000 rows and columns up to ES.

Thanks so so much in advance,
Petra
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

It would seem that using Ctrl H > Replace > Within Workbook ... would solve your problem ...:wink:

HTH
 

PetraK

New Member
Joined
Jun 12, 2018
Messages
6
Hi,

It would seem that using Ctrl H > Replace > Within Workbook ... would solve your problem ...:wink:

HTH

Thanks James - unfortunately this would take quite a bit of time because I have over 400 changes to make. Is there not a faster way than manually replace?
 

PetraK

New Member
Joined
Jun 12, 2018
Messages
6

PetraK

New Member
Joined
Jun 12, 2018
Messages
6
Hey Dave,

Thanks again for your help. So I have been trying to use your VBA - but am such a newbie that I am unclear how to get this going.

I have copied the code into the module - however, I'm unclear what parameters to change to make this fit my workbook.

Currently Sheet 1 contains all my data in different languages that I need to change to English (22k rows and columns to ES).

In sheet 2, I have two columns, the first column (A1 = Original; from A2 has the sentences to search for) contains the local language text e.g. Lundi. And the second column (B1 = Replacement; from B2 has the English sentences that will replace the originals) contains the English.

Sorry to be such a newbie but how do I alter the below to make it fit my scenario?

Thanks,
Petra

Sub Multi_FindReplace()
'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


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


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


'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


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


End Sub
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,932
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi,
link to publish code was just a suggestion for you to try, I had no involvement writing it.

Appreciate VBA can seem daunting but it can be just as challenging for contributors who have to try and interpret how a workbook is laid out & what OP wants to see as an outcome. For most requirements, this can be quite straightforward but for ones like yours, it helps if you are able to provide a copy of your workbook with some sample data.

To do this you would need to place in a dropbox file (MrExcel has no facility for attaching a workbook) & place a link to it here. I and others hopefully, should be able to offer more guidance.


Dave
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,932
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try following & see if does what you want

Place code in a STANDARD module.

Code:
Sub FindAndReplace()
    Dim wsList As Worksheet, wsMaster As Worksheet
    Dim FindAndReplaceData As Variant, MasterData As Variant
    Dim i As Long
    
    With ThisWorkbook
        Set wsMaster = .Worksheets("Sheet1")
        Set wsList = .Worksheets("Sheet2")
    End With
        
    FindAndReplaceData = wsList.UsedRange.Value2
    MasterData = wsMaster.UsedRange.Value2
    
    With Application
        .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlCalculationManual
    End With
    
    For i = 1 To UBound(FindAndReplaceData, 1)
        wsMaster.UsedRange.Replace What:=FindAndReplaceData(i, 1), _
        ReplaceMent:=FindAndReplaceData(i, 2), LookAt:=xlWhole, MatchCase:=False
    Next
    
    With Application
        .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlCalculationAutomatic
    End With
End Sub

Be aware that with such large amount of data, code may take awhile to run.

Make a backup of your data before testing.

Dave
 

PetraK

New Member
Joined
Jun 12, 2018
Messages
6
Dave - sorry for the late reply.

You, my good sir, are a genius!! Thank you SO SO much for your help with this. I cannot thank you enough!! Thank you, thank you, thank you!!!! I am eternally grateful!

Petra
 

Forum statistics

Threads
1,148,293
Messages
5,745,912
Members
423,983
Latest member
blackworx

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