Automatization in find and replace with VBA

elvy

New Member
Joined
Dec 13, 2019
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hello. Maybe someone would know the possibility to make find and replace in auto way with VBA?
I have a sheet with the content in two languages (this is example and there are rows in the original file):
1610441698377.png


And I have another sheet where there are loads of reponse values in different languages all mixed up (this is example and there are values in the original file)::
1610441682269.png



How could I automatically find the value from the 1st example for instance "Hello" and to change it in the 2nd example to the value "Labas"?
And to make it for all values from the 1st example Column A, to Column B in the 2nd example?

Thank you!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
Maybe this:

VBA Code:
Sub a1158193a()
'https://www.mrexcel.com/board/threads/automatization-in-find-and-replace-with-vba.1158193/

Dim i As Long, va
va = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)

For i = 1 To UBound(va, 1)
    Sheets("Sheet2").Cells.Replace What:=va(i, 1), Replacement:=va(i, 2), LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next

End Sub

DATA:
Book2
AB
2Carxx
3YOUyy
4Wezz
Sheet1


Book2
ABCD
2CarCarYOU
3YOUWeCar
4YOUCarWe
Sheet2


RESULT:

Book2
ABCD
2xxxxyy
3yyzzxx
4yyxxzz
Sheet2


Note:
1. Sheet1 must be the active sheet when you run the macro.
2. Change Sheets("Sheet2") as needed
 

elvy

New Member
Joined
Dec 13, 2019
Messages
15
Office Version
  1. 365
Platform
  1. Windows
I am working with a file that has around 6000 values and in this file, there are responses to the quiz in two languages, I want to quickly translate and make the list only in one language to create a clear pivot table and this code takes a lot of time to do for such a big database or I am doing something wrong.. :(

So I have around 6000 values and I have around 15 translations to do it and I needed to end this macro because took so long and not sure it worked correctly.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
You should try it on a small sample first, to see whether it works or not.
And try this amended version:
VBA Code:
Sub a1158193b()
'https://www.mrexcel.com/board/threads/automatization-in-find-and-replace-with-vba.1158193/

Dim i As Long, va
va = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)

Application.ScreenUpdating = False
Application.EnableEvents = False

    For i = 1 To UBound(va, 1)
        Sheets("Sheet2").Cells.Replace What:=va(i, 1), Replacement:=va(i, 2), LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Next

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

So I have around 6000 values and I have around 15 translations
What do you mean by 6000 values & 15 translation?
Do you have formula in the sheet?
 

elvy

New Member
Joined
Dec 13, 2019
Messages
15
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It did not work on a small sample this.
I have this file where there are two languages:


And I have this sheet with the translations:

In a sheet1 it all the A column sentences needs to be in B column in the sheet1. What I was doing is just manually find and replace but it its not efficient :/

EDIT: screen shots removed at OP's request.
 
Last edited by a moderator:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
It looks like the 2 sheets are in reversed position.
If you see my example in post 2, "Sheet1" is where the list of translation is located, but in your example above it is in "Sheet2". So you need to fix it first.
Did you try the example in post #2? Did it work?
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
Could you upload a sample workbook to a free site such as dropbox.com or google drive & then share the link here? It will make it easier to test and find a solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,135
Messages
5,640,318
Members
417,136
Latest member
reeton3

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