Excel Macro to Find & Replace Diacritic Characters

[excel8442]

I am trying the following but getting an error (on the row with --> ActiveDocument.Range.Find.Execute) in Excel 2011 for Mac. Any ideas?

Sub Diacritics_Replacement()


Dim strFind As String
Dim strReplace As String
Dim i As Long
strFind = "áéíóú"
strReplace = "aeiou"
For i = 1 To Len(strFind)
ActiveDocument.Range.Find.Execute FindText:=Mid$(strFind, i, 1), _
ReplaceWith:=Mid$(strReplace, i, 1), Replace:=wdReplaceAll
Next i


End Sub

 

[Hansulet]

how can I add other characters to be replaced ?

 

[Rick Rothstein]

how can I add other characters to be replaced ?

Using the code I posted in Message #12, simply add the characters to be replaced to the end of the quoted characters assigned to the Diacritic variable and then add the replacement characters (in the same order) to the end of the quoted characters assigned to the Normal variable. In the example below, I am replacing the § character with an S, the ß character with a B and the Ǝ character with an E (I have added these characters to the each variable in the correct order and highlighted them in red so you can see how I have done this... you may have to scroll the code window left-to-right in order to see it)...

[table="width: 500"]
[tr]
	[td]Sub ReplaceDiacriticCharacters()
  Dim X As Long, Diacritic As Variant, Normal As Variant
  Diacritic = Split(Trim(Replace(StrConv("ÀÁÂÃÄÅàáâãäåÈÉÊËèéêëÌÍÎÏìíîïÑñÒÓÔÕÖòóôõöÙÚÛÜùúûüÝýÿ[B][COLOR="#FF0000"]§ßƎ[/COLOR][/B]", vbUnicode), Chr(0), " ")))
  Normal = Split(Trim(Replace(StrConv("AAAAAAaaaaaaEEEEeeeeIIIIiiiiNnOOOOOoooooUUUUuuuuYyy[B][COLOR="#FF0000"]SBE[/COLOR][/B]", vbUnicode), Chr(0), " ")))
  For X = 0 To UBound(Diacritic)
    ActiveSheet.UsedRange.Replace Diacritic(X), Normal(X), xlPart, MatchCase:=True
  Next
End Sub[/td]
[/tr]
[/table]

 

[excel8442]

Application.Replaceformat does not exist in Macintosh VBA:
http://www.realgeek.com/forums/replaceformat-426603.html

 

[Hansulet]

I have an error when I apply this macro. All characters from my sheet are are replaced with one of characters mentioned in macro (from the third row). What can I do to correct this error?

 

[Rick Rothstein]

I have an error when I apply this macro. All characters from my sheet are are replaced with one of characters mentioned in macro (from the third row). What can I do to correct this error?

Let's start slowly...

All characters? Even normal letters?

Which macro (tell us the Message Number the macro is located in)?

Which character is everything being replaced with?

What is in your cells... constants or formulas?

 

[Hansulet]

Yes, all characters, even normal letters. I refer to the macro posted on 01.04.2016 (item #22). All characters are replaced with "N". Some cells contains formulas and some cells contains constants. Even cells formatted as data are replaced with NNNNNNNN

 

[Rick Rothstein]

Yes, all characters, even normal letters. I refer to the macro posted on 01.04.2016 (item #22). All characters are replaced with "N". Some cells contains formulas and some cells contains constants. Even cells formatted as data are replaced with NNNNNNNN

Hmm! I just ran it and everything was replaced with an E for me. The problem is that somehow a Step 2 got omitted from the For statement. This corrected code should now work for you...

[table="width: 500"]
[tr]
	[td]Sub ReplaceDiacriticCharacters()
  Dim X As Long, Diacritic As Variant, Normal As Variant
  Diacritic = Split(Trim(Replace(StrConv("ÀÁÂÃÄÅàáâãäåÈÉÊËèéêëÌÍÎÏìíîïÑñÒÓÔÕÖòóôõöÙÚÛÜùúûüÝýÿ§ß?", vbUnicode), Chr(0), " ")))
  Normal = Split(Trim(Replace(StrConv("AAAAAAaaaaaaEEEEeeeeIIIIiiiiNnOOOOOoooooUUUUuuuuYyySBE", vbUnicode), Chr(0), " ")))
  For X = 0 To UBound(Diacritic) [B][COLOR="#0000FF"]Step 2[/COLOR][/B]
    ActiveSheet.UsedRange.Replace Diacritic(X), Normal(X), xlPart, MatchCase:=True
  Next
End Sub[/td]
[/tr]
[/table]

 

[Hansulet]

Sorry, but I have the same result. N for everything. NNNN for all cells.

 

[Rick Rothstein]

Sorry, but I have the same result. N for everything. NNNN for all cells.

Hmm, the code I posted was tested before I posted it and it worked fine for me. Just out of curiosity, are you using a Mac? If so, it is possible that maybe the StrConv function does not work on a Mac... it should because it is part of the VB language, but I have run into other things VB commands that work on a PC (what I use) but not on a Mac and StrConv is only function that I can think of that might possibly have PC-only roots.

 

[Hansulet]

Oh, Sorry but it is clear that I am wrong because I work on a windows sysem. I apologize once more.