Find and Replace for ASCII Characters

[RachCamp7]

I am very new to VBA, but I am learning so please bear with me. I'm creating a "validation/reformatting" tool for a team at work. I am stuck.

I want to create a code for finding a specific list of ASCII characters in cells and replacing them. For Example: If "Ä" appears in a cell, I want it to be replaced with "A". I don't want it to remove anything else in that cell, just that one character. I've created a "table" tab that has a list of the specific ASCII codes and the replacement character.

Please help!

 

[RickXL]

Hi and welcome to the MrtExcel Message Board.

The macro recorder would have helped with this. If you switched it on then went into theHome-->Editing-->Find & Select--> Replace ... menu then it would have recorded some Replace commands for you.

Then I like to make sure that they are going to work on the right worksheet and not just any one that happens to be active but this depends on how you need it to work. So I might add to the recording as follows:

Sub MakeChanges()
    With ThisWorkbook.Worksheets("Sheet1")
        .Cells.Replace What:="a", Replacement:="b", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
        .Cells.Replace What:="5", Replacement:="6", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
        .Cells.Replace What:="z", Replacement:="A1", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
         .Cells.Replace What:="Z", Replacement:="S2", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
    End With
End Sub

That changes "a" into "b", "5" into "6", "z" into "A1" and "Z" into "S2". Just substitute the changes you need and add commands as necessary.