VBA // replace foreing characters with English equivalent

DonAndress

Active Member
Joined
Sep 25, 2011
Messages
362
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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:

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":
Code:
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?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Forum statistics

Threads
1,215,325
Messages
6,124,252
Members
449,149
Latest member
mwdbActuary

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
Back
Top