Matching two cells from different sheets & replacing it with another cells value HELP

[MurraySMTH]

Hello all,

I am having trouble with my code. I am trying to write a code that recognizes that Sheet 4 column A&B match each other though they contain different data.

So if column A on "sheet 4" and column J on sheet "Template upload" match, then paste column B on Sheet 4 to replace column J on Sheet "template upload".

Sheet 4 Sheet "Template Upload"

My code better explains the method i am going for.

Sub LocationCodes_to_ColumnJ()

a = Worksheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To a

Next

If Worksheets("Sheet4").Cells(i, 1).Value = Worksheets("Sheet4").Cells(i, 2).Value Then


Worksheets("Sheet4").Cells(i, 2).Value = Worksheets("Template Upload").Cells(i, 10).Value

Worksheets("Sheet4").Cells(i, 2).Copy

Destination = Worksheets("Template Upload").Cells(i, 10)

End If

End Sub

 

[jasonb75]

See if this does what you need. Please test on a copy of your file to prevent data loss.

Sub LocationCodes_to_ColumnJ()
Dim c As Range, rng As Range, fRng As Range, rFound As Range
With Worksheets("Sheet 4")
    Set rng = Intersect(.Range("A:A"), .UsedRange)
End With
With Worksheets("Template Upload")
    Set fRng = Intersect(.Range("J:J"), UsedRange)
End With
For Each c In rng
    Set rFound = fRng.Find(c.Value, , xlValues, xlWhole, , , False)
    If Not rFound Is Nothing Then rFound.Value = c.Offset(0, 1).Value
Next
End Sub

 

[MurraySMTH]

See if this does what you need. Please test on a copy of your file to prevent data loss.

Sub LocationCodes_to_ColumnJ()
Dim c As Range, rng As Range, fRng As Range, rFound As Range
With Worksheets("Sheet 4")
    Set rng = Intersect(.Range("A:A"), .UsedRange)
End With
With Worksheets("Template Upload")
    Set fRng = Intersect(.Range("J:J"), UsedRange)
End With
For Each c In rng
    Set rFound = fRng.Find(c.Value, , xlValues, xlWhole, , , False)
    If Not rFound Is Nothing Then rFound.Value = c.Offset(0, 1).Value
Next
End Sub

This worked perfectly, but what if i have more than one match in column J. so if column J said:

Dog
Dog
Cat
Cat

How would i get it to replace all of the Dogs and Cats with the same data from column B. of "sheet4"

 

[jasonb75]

A quick edit, untested

Sub LocationCodes_to_ColumnJ()
Dim c As Range, rng As Range, fRng As Range, rFound As Range
With Worksheets("Sheet 4")
    Set rng = Intersect(.Range("A:A"), .UsedRange)
End With
With Worksheets("Template Upload")
    Set fRng = Intersect(.Range("J:J"), .UsedRange)
End With
For Each c In rng
        fRng.Replace CStr(c.Value), c.Offset(0, 1).Value, xlWhole, , False, False, False
Next
End Sub

One assumption is that the scenario below is not possible, when replacing C45 with L22, the code will not know which of the P45 cells started as PM and have already been changed, so the PM cells will be changed twice (once to C45, then to L22).

Book1
	A	B
1	PM	C45
2	CM	V67
3	TM	U89
4	C45	L22
Sheet14

 

[MurraySMTH]

yes perfect! thank you Jason. Sorry it took so long to get back to you. You are the best. Have a wonderful day!