Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
Find/Replace Event or Find/Replace for Protected Sheet ... | Excel Programming | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
Using Find and Replace to replace " in a macro | Excel Programming | |||
Replace method - cannot find any data to replace | Excel Programming |