Automatization in find and replace with VBA

[elvy]

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

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

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!

 

[Akuini]

Maybe this:

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
	A	B
2	Car	xx
3	YOU	yy
4	We	zz
Sheet1

Book2
	A	B	C	D
2		Car	Car	YOU
3	YOU		We	Car
4	YOU	Car		We
Sheet2

RESULT:

Book2
	A	B	C	D
2		xx	xx	yy
3	yy		zz	xx
4	yy	xx		zz
Sheet2

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

 

[elvy]

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]

You should try it on a small sample first, to see whether it works or not.
And try this amended version:

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]

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.

 

[Akuini]

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]

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.