From: JohnUK on
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

From: Don Guillett on
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
dguillett(a)gmail.com
"JohnUK" <JohnUK(a)discussions.microsoft.com> wrote in message
news:59E538C6-3C5D-40FA-9E75-6598228CD403(a)microsoft.com...
> 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
>

From: Jacob Skaria on
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
>
From: JohnUK on
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
> >
From: JohnUK on
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
> dguillett(a)gmail.com
> "JohnUK" <JohnUK(a)discussions.microsoft.com> wrote in message
> news:59E538C6-3C5D-40FA-9E75-6598228CD403(a)microsoft.com...
> > 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
> >
>
> .
>