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
|