VBA - effective replace function for many characters in certain range

[robo0502]

Hi guys,

I want to replace below characters in first raw in range("I3:P52") into those in second raw. Could you pls advise if there is an fast and effective way to do so? As you can see the range is 400 cells altogether.

ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ
SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy

thank you

Rob

 

[Joe4]

One way is to create a table with each value that you want to replace, and what you want to replace it with, then use the VBA code, like in the section entitled: Find And Replace Values At Once With VBA Code: https://www.extendoffice.com/docume...find-and-replace-multiple-values-at-once.html

 

[robo0502]

Hi Joe4, after small adjustment works perfectly.

thank you

 

[Joe4]

You are welcome!
Glad you got it working.

 

[Rick Rothstein]

Hi Joe4, after small adjustment works perfectly.

You can also do what you want without maintaining a conversion table on any of your worksheets. This wholly self-contained macro will also do what you want...

[table="width: 500"]
[tr]
	[td]Sub ReplaceAccentedLetters()
  Dim X As Long, LookFor As String, Replacements As String
  LookFor = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
  Replacements = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
  With Range("I3:P52")
    For X = 1 To Len(LookFor)
      .Replace Mid(LookFor, X, 1), Mid(Replacements, X, 1), xlPart, , True, , False, False
    Next
  End With
End Sub[/td]
[/tr]
[/table]

 

[robo0502]

thank you Rick