Find value from one sheet and replace value in second sheet
Great..
If this post helps click Yes
---------------
Jacob Skaria
"JohnM" wrote:
Thanks Jacob, this worked great! It was soooooo much simpler than what I was
trying to do in my script.
--
JohnM
"Jacob Skaria" wrote:
Hi John
Try the below and feedback
Sub FindandReplace()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long
Set ws1 = Worksheets("a")
Set ws2 = Worksheets("b")
For lngRow = 2 To ws1.Cells(Rows.Count, "B").End(xlUp).Row
If Trim(ws1.Range("b" & lngRow)) < "" And _
Trim(ws1.Range("k" & lngRow)) < "" Then _
ws2.Columns("J:J").Replace What:=ws1.Range("B" & lngRow), _
Replacement:=ws1.Range("k" & lngRow), LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
Next
End Sub
If this post helps click Yes
---------------
Jacob Skaria
"JohnM" wrote:
I am trying use VBA to:
locate the value that is in Sheet"a" column b, row 2
in Sheet"b" then
replace value in Sheet"b", column J with value from Sheet"a" column K
increment to Sheet"a" column b, row 3
Find matching value in Sheet"b"
replace value in Sheet"b", column J with value from Sheet"a" column K
Thanks in advance,
--
JohnM
|