Macro Involving Find & Replace In Two Different Files
 

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 havent found anything helpful yet in my googling. Any hints or advice
would be appreciated.

Thanks!
k

Macro Involving Find & Replace In Two Different Files
 

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" wrote:

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 havent found anything helpful yet in my googling. Any hints or advice
would be appreciated.

Thanks!
k

Macro Involving Find & Replace In Two Different Files
 

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

"Joel" wrote:

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" wrote:

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 havent found anything helpful yet in my googling. Any hints or advice
would be appreciated.

Thanks!
k

Macro Involving Find & Replace In Two Different Files
 

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 from:ot to:out 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" wrote:

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

"Joel" wrote:

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" wrote:

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 havent found anything helpful yet in my googling. Any hints or advice
would be appreciated.

Thanks!
k

Macro Involving Find & Replace In Two Different Files
 

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

"Joel" wrote:

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 from:ot to:out 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" wrote:

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

"Joel" wrote:

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" wrote:

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 havent found anything helpful yet in my googling. Any hints or advice
would be appreciated.

Thanks!
k