Prev: List Open Workbooks In All Excel Instances?
Next: open excel 2007 refresh query from batch file
From: JohnUK on 2 Jun 2010 11:44
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 2 Jun 2010 11:56
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 2 Jun 2010 12:30
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 3 Jun 2010 02:42
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 3 Jun 2010 03:08
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
> >
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: List Open Workbooks In All Excel Instances?
Next: open excel 2007 refresh query from batch file