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

MurraySMTH

Board Regular
Joined
Jun 30, 2020
Messages
76
Office Version
  1. 2016
Platform
  1. Windows
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"

1596158848402.png
1596158875522.png



My code better explains the method i am going for.


VBA Code:
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,886
Office Version
  1. 365
Platform
  1. Windows
See if this does what you need. Please test on a copy of your file to prevent data loss.
VBA Code:
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

Board Regular
Joined
Jun 30, 2020
Messages
76
Office Version
  1. 2016
Platform
  1. Windows
See if this does what you need. Please test on a copy of your file to prevent data loss.
VBA Code:
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,886
Office Version
  1. 365
Platform
  1. Windows
A quick edit, untested
VBA Code:
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
AB
1PMC45
2CMV67
3TMU89
4C45L22
Sheet14
 

MurraySMTH

Board Regular
Joined
Jun 30, 2020
Messages
76
Office Version
  1. 2016
Platform
  1. Windows
yes perfect! thank you Jason. Sorry it took so long to get back to you. You are the best. Have a wonderful day!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,964
Members
413,955
Latest member
FalcoDaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top