Clicky

Ok, I know you can do a simple find/replace but what I am looking for is a way to automate replacing several words with something else. For example, lets say I have a list of words and what to replace them with like this....

AAA = 111
BBB = 222
CCC = 333
DDD = 444

Some cells may have one word or two of the words but I need to replace all the words with the numbers (It's actually going to be to replace Japanese characters with the translated English word or phrase), they might be in the header or in individual cells.

Is there a way I can write a script o configure excel, macros etc. to replace a list of words or characters with English words?

The problem is I need to run a monthly report and one document might have 20 or more different words so I need a easy way I can just replace several words at once without doing a manual find/replace.

Thanks,

asked 12/15/2011 06:56

REIUSA's gravatar image

REIUSA ♦♦


3 Answers:
Just use these lines of code


    Cells.Replace What:="AAA", Replacement:="111", LookAt:=xlPart, MatchCase:=False,
    Cells.Replace What:="BBB", Replacement:="222", LookAt:=xlPart, MatchCase:=False,
    Cells.Replace What:="CCC", Replacement:="333", LookAt:=xlPart, MatchCase:=False,
link

answered

TommySzalapski's gravatar image

TommySzalapski

Create a tab called WordsToReplace and put in the first column (starting in row 1) the words to replace and in column 2 the replacement words, then run the attached macro.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
Sub ReplaceWords()
Dim lRowLoop As Long, lLastRow As Long, sht As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

With Sheets("WordsToReplace")

    lLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    
    For lRowLoop = 1 To lLastRow
        For Each sht In ActiveWorkbook.Worksheets
                
                If sht.Name <> .Name Then
                    sht.Cells.Replace What:=.Cells(lRowLoop, 1), Replacement:=.Cells(lRowLoop, 2), LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                        ReplaceFormat:=False
            
                End If
                
            
        Next sht
    Next lRowLoop

End With 'Sheets("NamesToReplace")

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True


Thomas
link

answered 2011-12-15 at 15:01:10

nutsch's gravatar image

nutsch

@nutsch: Cool thanks, that worked great.
link

answered 2011-12-15 at 15:05:02

REIUSA's gravatar image

REIUSA

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×1
×89
×24
×19
×3

Asked: 12/15/2011 06:56

Seen: 294 times

Last updated: 12/16/2011 12:02