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

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

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

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