Find and Replace Multiple Columns in Sheet 1 Based on a Column Values in Sheet 2

[adblog3]

I have been searching for this for whole day and since my vba knowledge is not that great, I am unable to make this thing happen. What I am looking to do is I have a sheet 1 with 4000 records, and sheet 2 contains regular updates that we do.

I am looking to find cell value of Column A value of Sheet 2 in Column M of Sheet 1 and if it is found then replace Column B and C for the respective row in Column M.

For e.g. In the sample sheet below, Shipment No and Shipment Date needs to be updated in Sheet 1, for the specific no. of items that are now updated in sheet 2. The unique column between two sheets is Document which is Column M in Sheet 1 and Column A in Sheet 2.

On a regular basis there are 3800-4000 permanent items in sheet 1, sheet 2 just contains shipment updates which are 30-50 daily.

Any help in this regard is highly appreciated.

 

[DanteAmor]

Hi and welcome to MrExcel.

Try this

Sub Find_Replace()
  Dim c As Range, f As Range
  For Each c In Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("A" & Rows.Count).End(3))
    Set f = Sheets("Sheet1").Range("M:M").Find(c, , xlValues, xlWhole)
    If Not f Is Nothing Then f.Offset(, -11).Resize(1, 2).Value = c.Offset(, 1).Resize(1, 2).Value
  Next
End Sub

 

[adblog3]

Thanks a ton Dante Amor, you have saved me a lot of trouble!

 

[adblog3]

Is it possible, to modify the macro to add a condition that if the shipment no is same as new one, don't change the date.