Macro Involving Find & Replace In Two Different Files

[K8_Dog]

Hi All,

I have some basic macro writing experience, and I would like to write a
macro that would perform a series of find and replace operations. The process
will involve two different Excel files - "Master" and "Corrections". Master
is my master document. Corrections has two columns: A contains mistakes that
are in the master; B contains their corrections.

Corrections file Example:

Mistakes Corrections
Colour Color
Humour Humor
Through Thru
Buses Busses

Basically I want to find mistakes in Master and replace them with the
corrections. In Master, the macro would be something like

Replace What:="colour", Replacement:="color†etc…

But instead of the words “colour†and “color†it would be a reference to
look at the “Corrections†file, perform find with A1, replace with A2, and
then move on to the next row.

I haven’t found anything helpful yet in my googling. Any hints or advice
would be appreciated.

Thanks!
k

 

[Joel]

this is easy to do. I would first like to know what type of file the
reference file is. I would recommend an excel file using sheet1. have the
old words in column A and the new words in Column b starting in row 1. let
me know if this is ok!

 

[K8_Dog]

Hi Joel,
That's exactly what I have. Thanks for your willingness to help.
k

 

[Joel]

You amy need to change the sheet names, but the code is very simple. I can
modify code to chaeck every sheet of the workbook.

the code is setup that the word lists are in the same workbook as the macro.
the code opens a pop up window to let you select the workbook to change.
then closes the modified workbook and saves changes. I have in the code to
look at part of the cell and not the entire cell. the reson for this is if
the word is in the middle of sentenance. There is a danger in doing this.
If you change fromt tout it will change also change from:hot to:hout.
You can replace xlpart to xlwhole.

Sub fix_sheets()

'default folder
Folder = "C:\temp"
ChDir (Folder)

Set fsread = CreateObject("Scripting.FileSystemObject")
FName = Application.GetOpenFilename("Excel File (*.xls),*.xls")

Set oldbk = Workbooks.Open(Filename:=FName)
With ThisWorkbook.Sheets("Sheet1")
RowCount = 2 'first row of word changes
Do While .Range("A" & RowCount) <> ""
oldword = .Range("A" & RowCount)
newword = .Range("B" & RowCount)
oldbk.Worksheets("Sheet1").Cells.Replace _
What:=oldword, _
Replacement:=newword, _
lookat:=xlPart, _
MatchCase:=False

RowCount = RowCount + 1
Loop

End With

oldbk.Close savechanges:=True
End Sub

 

[K8_Dog]

Thank you so much! This is exactly what I need. I haven't tried it out yet,
but I'm sure that based on your code, I can make my 1700 corrections easily!!
Thanks!
k