Solved: Replacing Text

[godsowndevil]

Hi,


Need to write a code to replace certain characters.

My current code:


Sub Replace_Text
Columns("A:CO").Select
Selection.Replace What:="?", Replacement:="A", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

This works fine for most cases, but some of my cells have more than 500-1000 characters & this does not work for those cells. Is there any way to overcome this difficulty?

[Simon Lloyd]

This works fine for most cases, but some of my cells have more than 500-1000 characters & this does not work for those cells. Is there any way to overcome this difficulty?What version of Excel are you using? you probably have to run the code a second time to catch the others.

[godsowndevil]

Hi,

I'm using Excel 2003. The replace works row-wise & hence it doesnt go past the cell that has more than 500 characters no matter how many times I run the macro. Also, when I try to put in characters like "Ĝ", VBA does not accept it & gives a "?" instead.

[Paul_Hossler]

Try this (I used B and Z but works on cells with over 500 chars)


Sub Replace_Text1()
Dim rCell As Range

On Error GoTo NiceExit

For Each rCell In ActiveSheet.Columns("A:CO").SpecialCells(xlCellTypeConstants, xlTextValues).Cells
With rCell
.Value = Application.WorksheetFunction.Substitute(.Value, "B", "Z")
End With
Next

NiceExit:
End Sub



If you want to replace the Unicode characters use ChrW


Sub Replace_Text2()
Dim rCell As Range

On Error GoTo NiceExit

For Each rCell In ActiveSheet.Columns("A:CO").SpecialCells(xlCellTypeConstants, xlTextValues).Cells
With rCell

'U+011C LATIN CAPITAL LETTER G WITH CIRCUMFLEX = 284 decimal
.Value = WorksheetFunction.Substitute(.Value, ChrW(284), "G")
End With
Next

NiceExit:
End Sub


Paul

[godsowndevil]

Thanks a lot... The Code is working fine...