VBA // replace foreing characters with English equivalent

[DonAndress]

Hello.

I have English Excel 2010 with additional Polish language installed (both for edit and for layout).

Now, I have a column with multiple names but these names are Polish, meaning, there are many Polish characters.
I'd like to remove those Polish letters with English equivalent, therefore I have a below code:

Dim PoleZzz()
Dim PoleBez()

PoleZzz = Array("ą", "ć", "ę", "ń", "ó", "ł", "ś", "ż", "ź") 'Polish
PoleBez = Array("a", "c", "e", "n", "o", "l", "s", "z", "z") 'English

For i = 0 To 8
    Workbooks(MacroWkb).Sheets("Data").Range("B14:B20").Replace  What:=PoleZzz(i), Replacement:=PoleBez(i), LookAt:=xlPart,  SearchOrder:=xlByColumns, MatchCase:=False
Next i

But this just doesn't work.
Seems like Excel (VBA) doesn't recognize Polish characters because this is what I got from recording the macro when I used Ctrl+H to replace "ą" with "a":

Selection.Replace What:="a", Replacement:="a", LookAt:=xlPart,  SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False,  ReplaceFormat:=False

The thing is that Excel replaces Polish characters without any problem.

Is there any workaround to force VBA to recognize it?

 

[FormR]

Hi,

I suspect that the VBA editor doesn't like Unicode characters. Does this work any better?

Sub m()


Dim PoleZzz()
Dim PoleBez()


'PoleZzz = Array("a", "c", "e", "n", "ó", "l", "s", "z", "z")
PoleZzz = Array(261, 263, 281, 324, 243, 322, 347, 380, 378) 'Polish UNICODE code point
PoleBez = Array("a", "c", "e", "n", "o", "l", "s", "z", "z") 'English


For i = 0 To 8
    Workbooks(MacroWkb).Sheets("Data").Range("B14:B20").Replace What:=ChrW(PoleZzz(i)), Replacement:=PoleBez(i), LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
Next i


End Sub

 

[Comfy]

Try something like this:

Sub test()
Dim PoleZzz()
Dim PoleBez()


PoleZzz = Array(261, 263, 281, 324, 243, 322, 347, 380, 378) 'Polish
PoleBez = Array(97, 99, 101, 110, 111, 108, 115, 122, 122) 'English


For i = 0 To 8
    ActiveSheet.Range("G2:G10").Replace What:=ChrW(PoleZzz(i)), Replacement:=ChrW(PoleBez(i)), LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
Next i
End Sub

 

[DonAndress]

Absolutely peachy!
Both codes work like a charm, thank you.