Find value from one sheet and replace value in second sheet
 

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

Find value from one sheet and replace value in second sheet
 

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

Find value from one sheet and replace value in second sheet
 

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

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