Excel VBA: Can't get the "Replace" method to work... 2

[patrok]

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.

 

[xlbo]

Check what ENname and FRname actually contain. Step through the code using F8 and hover over the variables after they have been popultaed - what are their values?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[patrok]

Hi Geoff,

Yes, I did do the debug using F8 : as the "translation" sheet is being parsed, you can see the values of ENname and FRname being updated just as they should.

For instance:
- WORKS CORRECTLY:
ENname="Brussels"
FRname="Bruxelles"
Worksheets("Geo").Columns("I:L").Replace _
What:=ENname, Replacement:=FRname, LookAt:=xlPart, _
SearchOrder:=xlWhole, MatchCase:=True
==> "Brussels" is correctly changed to "Bruxelles"

- DOESN'T WORK:
ENname = Selection.Text // Debugger: ENname="Brussels"
Selection.Offset(0, 1).Select
FRname = Selection.Text // Debugger: FRname="Bruxelles"
Selection.Offset(1, -1).Select
==> the exact same "replace" method as above doesn't work anymore (i.e. "Brussels" is not changed to "Bruxelles" anymore)...

I am lost... Thanks for any hint!

Cheers,
patrick.

 

[xlbo]

Just to be sure - you changed the text back again before running it for the 2nd time didn't you ?



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[patrok]

Just found my mistake!

ENname="Brussels " and not "Brussels"... The difference is subtle, but obviously vital...

Thanks!
Patrick.

 

[PHV]

So, a paranoid method:
Worksheets("Geo").Columns("I:L").Replace _
What:=[!]Trim([/!]ENname[!])[/!], Replacement:=[!]Trim([/!]FRname[!])[/!], LookAt:=xlPart, _
SearchOrder:=xlWhole, MatchCase:=[!]False[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886