Solved: Replacing Text

[godsowndevil]

Hi,


Need to write a code to replace certain characters.

My current code:

[VBA]
Sub Replace_Text
Columns("A:CO").Select
Selection.Replace What:="?", Replacement:="A", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
[/VBA]
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)

[vba]
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
[/vba]


If you want to replace the Unicode characters use ChrW

[VBA]
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
[/VBA]

Paul

[godsowndevil]

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