Find and Replace
 

Hi,
I am after a piece of code that can replace words from one tab to the next
For example:
Sheet1 - pick up the first word from the Wrong Column(€œWrong1€�) to find and
replace that word in Sheet2 with the correct word found in the Correct column
in Sheet1(€œCorrect 1€�)
Then keep going until it reaches the last word within the Wrong column of
Sheet1

Wrong Column Correct Column
Wrong 1 Correct 1
Wrong 2 Correct 2
Wrong 3 Correct 3
Wrong 4 Correct 4
Wrong 5 Correct 5
Wrong 6 Correct 6
Wrong 7 Correct 7

My goodness, I hope that makes sense

Help greatly appreciated

Regards
John

Find and Replace
 

Not really
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnUK" wrote in message
...
Hi,
I am after a piece of code that can replace words from one tab to the next
For example:
Sheet1 - pick up the first word from the Wrong Column(€œWrong1€�) to find
and
replace that word in Sheet2 with the correct word found in the Correct
column
in Sheet1(€œCorrect 1€�)
Then keep going until it reaches the last word within the Wrong column of
Sheet1

Wrong Column Correct Column
Wrong 1 Correct 1
Wrong 2 Correct 2
Wrong 3 Correct 3
Wrong 4 Correct 4
Wrong 5 Correct 5
Wrong 6 Correct 6
Wrong 7 Correct 7

My goodness, I hope that makes sense

Help greatly appreciated

Regards
John

Find and Replace
 

Hope this will point you in the right direction...

Sub FindandReplace()
Dim ws1 As Worksheet, ws2 As Worksheet, lngRow As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

For lngRow = 1 To ws1.Cells(Rows.Count, "B").End(xlUp).Row
If Trim(ws1.Range("B" & lngRow)) < "" And _
Trim(ws1.Range("A" & lngRow)) < "" Then _
ws2.Cells.Replace What:=ws1.Range("A" & lngRow), _
Replacement:=ws1.Range("B" & lngRow), LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
Next

End Sub


--
Jacob (MVP - Excel)


"JohnUK" wrote:

Hi,
I am after a piece of code that can replace words from one tab to the next
For example:
Sheet1 - pick up the first word from the Wrong Column(€œWrong1€�) to find and
replace that word in Sheet2 with the correct word found in the Correct column
in Sheet1(€œCorrect 1€�)
Then keep going until it reaches the last word within the Wrong column of
Sheet1

Wrong Column Correct Column
Wrong 1 Correct 1
Wrong 2 Correct 2
Wrong 3 Correct 3
Wrong 4 Correct 4
Wrong 5 Correct 5
Wrong 6 Correct 6
Wrong 7 Correct 7

My goodness, I hope that makes sense

Help greatly appreciated

Regards
John

Find and Replace
 

Many thanks Jacab
Fantastic!! Exactly what I wanted
Kind Regards
John

"Jacob Skaria" wrote:

Hope this will point you in the right direction...

Sub FindandReplace()
Dim ws1 As Worksheet, ws2 As Worksheet, lngRow As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

For lngRow = 1 To ws1.Cells(Rows.Count, "B").End(xlUp).Row
If Trim(ws1.Range("B" & lngRow)) < "" And _
Trim(ws1.Range("A" & lngRow)) < "" Then _
ws2.Cells.Replace What:=ws1.Range("A" & lngRow), _
Replacement:=ws1.Range("B" & lngRow), LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
Next

End Sub


--
Jacob (MVP - Excel)


"JohnUK" wrote:

Hi,
I am after a piece of code that can replace words from one tab to the next
For example:
Sheet1 - pick up the first word from the Wrong Column(€œWrong1€�) to find and
replace that word in Sheet2 with the correct word found in the Correct column
in Sheet1(€œCorrect 1€�)
Then keep going until it reaches the last word within the Wrong column of
Sheet1

Wrong Column Correct Column
Wrong 1 Correct 1
Wrong 2 Correct 2
Wrong 3 Correct 3
Wrong 4 Correct 4
Wrong 5 Correct 5
Wrong 6 Correct 6
Wrong 7 Correct 7

My goodness, I hope that makes sense

Help greatly appreciated

Regards
John

Find and Replace
 

Thanks for offerering your help Don, but Jacob had just what I wanted
Regards
John

"Don Guillett" wrote:

Not really
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnUK" wrote in message
...
Hi,
I am after a piece of code that can replace words from one tab to the next
For example:
Sheet1 - pick up the first word from the Wrong Column(€œWrong1€�) to find
and
replace that word in Sheet2 with the correct word found in the Correct
column
in Sheet1(€œCorrect 1€�)
Then keep going until it reaches the last word within the Wrong column of
Sheet1

Wrong Column Correct Column
Wrong 1 Correct 1
Wrong 2 Correct 2
Wrong 3 Correct 3
Wrong 4 Correct 4
Wrong 5 Correct 5
Wrong 6 Correct 6
Wrong 7 Correct 7

My goodness, I hope that makes sense

Help greatly appreciated

Regards
John


.