Find value from one sheet and replace value in second sheet

[JohnM]

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,

 

[Jacob Skaria]

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

 

[JohnM]

Thanks Jacob, this worked great! It was soooooo much simpler than what I was
trying to do in my script.

 

[Jacob Skaria]

Great..

If this post helps click Yes