Hi there,
I have a excel spreadsheet with 2 sheets:
- "Geo" sheet: containing name of places (columns I to L in French and columns M to P in English)
- "Translation" sheet: contain the translation between French and English (Column A=name in English; column B=name in French)
My goal is to use the Translation sheet to translate names in the Geo sheet. Here is the Sub I created:
Sub Translate()
Dim FRname As String
Dim ENname As String
Sheets("Translation").Select
Range("A2").Select
Do Until Selection.Text = ""
ENname = Selection.Text
Selection.Offset(0, 1).Select
FRname = Selection.Text
Selection.Offset(1, -1).Select
Worksheets("Geo").Columns("I:L").Replace _
What:=ENname, Replacement:=FRname, LookAt:=xlPart, _
SearchOrder:=xlWhole, MatchCase:=True
Worksheets("Geo").Columns("M
").Replace _
What:=FRname, Replacement:=ENname, LookAt:=xlPart, _
SearchOrder:=xlWhole, MatchCase:=True
Loop
End Sub
MY PROBLEM:
"What:=ENname, Replacement:=FRname" does not seem to work : did I forget something in the syntax ?
The sub works just fine if I replace it by:
What:="OneNameinEnglish", Replacement:="ThatNameinFrench"
But obviously, I want to use variables and not fixed strings...
Thanks for any help!!!
Kind regards,
Patrick.
I have a excel spreadsheet with 2 sheets:
- "Geo" sheet: containing name of places (columns I to L in French and columns M to P in English)
- "Translation" sheet: contain the translation between French and English (Column A=name in English; column B=name in French)
My goal is to use the Translation sheet to translate names in the Geo sheet. Here is the Sub I created:
Sub Translate()
Dim FRname As String
Dim ENname As String
Sheets("Translation").Select
Range("A2").Select
Do Until Selection.Text = ""
ENname = Selection.Text
Selection.Offset(0, 1).Select
FRname = Selection.Text
Selection.Offset(1, -1).Select
Worksheets("Geo").Columns("I:L").Replace _
What:=ENname, Replacement:=FRname, LookAt:=xlPart, _
SearchOrder:=xlWhole, MatchCase:=True
Worksheets("Geo").Columns("M
What:=FRname, Replacement:=ENname, LookAt:=xlPart, _
SearchOrder:=xlWhole, MatchCase:=True
Loop
End Sub
MY PROBLEM:
"What:=ENname, Replacement:=FRname" does not seem to work : did I forget something in the syntax ?
The sub works just fine if I replace it by:
What:="OneNameinEnglish", Replacement:="ThatNameinFrench"
But obviously, I want to use variables and not fixed strings...
Thanks for any help!!!
Kind regards,
Patrick.