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

 

[excel8442]

Very helpful -- many thanks!

Maybe this would help:
http://www.mrexcel.com/forum/excel-...nted-characters-regular-characters-excel.html

 

[excel8442]

Thanks Rick!

Here is my macro with the characters from the last message at the link you posted...

[TABLE="width: 500"]
<tbody>[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("AAAAAAaaaaaaEEEEeeeeIIIIiiiiNnOOOOOoooooUUUUuuuuYyy", vbUnicode), Chr(0), " ")))
  For X = 0 To UBound(Diacritic)
    ActiveSheet.UsedRange.Replace Diacritic(X), Normal(X), xlPart, MatchCase:=True
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[excel8442]

I find that when I run this (working) macro I end up with some incorrect diacritic replacements (due to errors in my source data probably).

I'd like to:
- run the macro to do all replacements en masse
- "mark" the cells that contain any replacements in some way (colour the cells "red" perhaps?)
- manually "cycle through" the red cells and manually "fix" any problems

How do I colour the cells in the macro and then cycle through the red cells?

 

[Rick Rothstein]

I find that when I run this (working) macro I end up with some incorrect diacritic replacements (due to errors in my source data probably).

You can copy the two quoted strings from the assignment lines to Diacritic and Normal and paste them into the Immediate Window, one under the other... the diacritic characters and their replacement characters will line up, one under the other, and you can check if you made a mistake in setting those text strings up. If they line up correctly, then you must have missed accounting for some diacritic characters... locate them and add them, and their replacements, into the two text strings (just make sure the diacritic and its replacement line up one under the other.

I'd like to:
- run the macro to do all replacements en masse
- "mark" the cells that contain any replacements in some way (colour the cells "red" perhaps?)
- manually "cycle through" the red cells and manually "fix" any problems

I don't understand this... the only characters I can test are already accounted for in the two text strings... if you missed any diacritic characters, the code will not be able to detect them so those cells would remain uncolored.

 

[excel8442]

This isn't the issue. Nothing wrong with your macro at all or either list of chars.

I have peculiarities in the source file. MOST of the time your macro will do what I want. In (unpredictable) cases the resulting replacement will not be the char I actually want (entirely unpredictable). Because of this I need to manually check after-the-fact (after your macro has run). I'd prefer to do it AFTER your macro has run because your macro will be correct 90% of the time.

So my idea is:
-1- run (a modified version) of your macro to replace Diacritics AND WHICH COLORS THE CELLS "RED" IF ANY CHARS WERE REPLACED.
-2- I manually FIND based on cell fill color = RED and eyeball the cell contents and fix any bad chars manually.
-3- I then select all and recolor everything to "No Color"

Since then I've encountered a "complication" to point #2 above:

I am running Excel 2011 for Mac and (apparently unlike on Windows) there is no automatic way to FIND based on CELL COLOR -- is that correct?

Hope this makes sense. Perhaps you have a better procedure.

 

[excel8442]

Hope this makes sense. Perhaps you have a better procedure.

I have a couple of other possible "ideas" but I don't know if they can be done:

-1- Use FILTER rather than FIND. If I could filter to see just the cells (rows) that CONTAIN 1 or more chars from ÀÁÂÃÄÅàáâãäåÈÉÊËèéêëÌÍÎÏìíîïÑñÒÓÔÕÖòóôõöÙÚÛÜùúûüÝýÿ then I could FIX my problem items simply before running your macro (the un-modified version as above). That would be a simple solution perhaps.

-2- Similarly, use FILTER rather than FIND to see just the RED Cells (Rows) that have been coloured RED by a modified version of your macro.

 

[Rick Rothstein]

-1- run (a modified version) of your macro to replace Diacritics AND WHICH COLORS THE CELLS "RED" IF ANY CHARS WERE REPLACED.

Run this macro BEFORE you run the one that does the replacing (otherwise there will be no diacritic characters to find)...

[table="width: 500"]
[tr]
	[td]Sub RedDiacritics()
  Dim X As Long, Diacritics() As String
  Diacritics = Split(StrConv("ÀÁÂÃÄÅàáâãäåÈÉÊËèéêëÌÍÎÏìíîïÑñÒÓÔÕÖòóôõöÙÚÛÜùúûüÝýÿ", vbUnicode), Chr(0))
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.Color = vbRed
  Application.ScreenUpdating = False
  For X = 0 To UBound(Diacritics) - 1
    ActiveSheet.UsedRange.Replace Diacritics(X), "", xlPart, SearchFormat:=False, ReplaceFormat:=True
  Next
  Application.ScreenUpdating = True
  Application.ReplaceFormat.Clear
End Sub[/td]
[/tr]
[/table]

 

[excel8442]

Run this macro BEFORE you run the one that does the replacing (otherwise there will be no diacritic characters to find)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub RedDiacritics()
  Dim X As Long, Diacritics() As String
  Diacritics = Split(StrConv("ÀÁÂÃÄÅàáâãäåÈÉÊËèéêëÌÍÎÏìíîïÑñÒÓÔÕÖòóôõöÙÚÛÜùúûüÝýÿ", vbUnicode), Chr(0))
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.Color = vbRed
  Application.ScreenUpdating = False
  For X = 0 To UBound(Diacritics) - 1
    ActiveSheet.UsedRange.Replace Diacritics(X), "", xlPart, SearchFormat:=False, ReplaceFormat:=True
  Next
  Application.ScreenUpdating = True
  Application.ReplaceFormat.Clear
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for this code.

I'm getting a runtime error 438 at the line: Application.ReplaceFormat.Clear
(Object doesn't support this property or method).

If I delete that line I get the same error message on the next line...

 

[Rick Rothstein]

Thanks for this code.

I'm getting a runtime error 438 at the line: Application.ReplaceFormat.Clear
(Object doesn't support this property or method).

If I delete that line I get the same error message on the next line...

What version of Excel are you using?

Are you running Excel on a Mac?

 

[excel8442]

I have Excel 2011 for Mac 14.6.2 on OSX El Capitan 10.11.3