Multiple Search and Find Problem

[PetraK]

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

 

[James006]

Hi,

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

HTH

 

[PetraK]

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?

 

[dmt32]

Hi,
have a look here:https://www.thespreadsheetguru.com/the-code-vault/2014/4/14/find-and-replace-all

and see if the article gives you something you can work with.

Hopefully, will go in right direction but can always post back if need further assistance.

Dave

 

[PetraK]

Hi,
have a look here:https://www.thespreadsheetguru.com/the-code-vault/2014/4/14/find-and-replace-all

and see if the article gives you something you can work with.

Hopefully, will go in right direction but can always post back if need further assistance.

Dave

Thanks for this Dave! I will check it out and muddle my through. I will defo come back if still struggling - thank you!

 

[PetraK]

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]

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

 

[PetraK]

Thanks Dave. Appreciate the advice - again being new I am not aware of what's needed.

I have created the requested: https://www.dropbox.com/s/ybctln98m...A FILE - CONVERTING ENGLISH EXAMPLE.xlsm?dl=0

Hopefully this can be accessed ok. Thanks in advance. Very much appreciate any advice,
Petra

 

[dmt32]

Hi,
try following & see if does what you want

Place code in a STANDARD module.

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]

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