VBA - Script to search and replace values in rows between sheets based on criteria

[RobertHamberg]

I want ascript to search for the values in column A in sheet 1 and try to find a match in column A in Sheet 2. If a match is found I want the script to copy the values in column F-J in sheet 1 and add those values to column F-J in sheet 2 on the matching rows. Anyone able to help?

Sheet 1[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: cke_show_border"]
<tbody>[TR]
[TD][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 126"]B[/TD]
[TD="width: 73"]C[/TD]
[TD="width: 73"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 147"]F[/TD]
[TD="width: 64"]G[/TD]
[TD="width: 64"]H[/TD]
[TD="width: 64"]I[/TD]
[TD="width: 64"]J[/TD]
[/TR]
[TR]
[TD="width: 64"]1[/TD]
[TD="width: 64"]Name[/TD]
[TD="width: 126"]Owner[/TD]
[TD="width: 73"]Start[/TD]
[TD="width: 73"]End[/TD]
[TD="width: 64"]Time (H)[/TD]
[TD="width: 147"]Team[/TD]
[TD="width: 64"]R1[/TD]
[TD="width: 64"]%[/TD]
[TD="width: 64"]R2[/TD]
[TD="width: 64"]%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Test2[/TD]
[TD]Owner2[/TD]
[TD="align: right"]2018-01-01[/TD]
[TD="align: right"]2018-12-31[/TD]
[TD="align: right"]200[/TD]
[TD]Team2[/TD]
[TD]Resurs2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Test3[/TD]
[TD]Owner3[/TD]
[TD="align: right"]2018-01-01[/TD]
[TD="align: right"]2018-12-31[/TD]
[TD="align: right"]300[/TD]
[TD]Team3[/TD]
[TD]Resurs3[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Test5[/TD]
[TD]Owner5[/TD]
[TD="align: right"]2018-01-01[/TD]
[TD="align: right"]2018-12-31[/TD]
[TD="align: right"]500[/TD]
[TD]Team5[/TD]
[TD]Resurs5[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Test6[/TD]
[TD]Owner6[/TD]
[TD="align: right"]2018-01-01[/TD]
[TD="align: right"]2018-12-31[/TD]
[TD="align: right"]600[/TD]
[TD]Team6[/TD]
[TD]Resurs6[/TD]
[TD="align: right"]75[/TD]
[TD]Resurs3[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Test8[/TD]
[TD]Owner8[/TD]
[TD="align: right"]2018-01-01[/TD]
[TD="align: right"]2018-12-31[/TD]
[TD="align: right"]800[/TD]
[TD]Team8[/TD]
[TD]Resurs8[/TD]
[TD="align: right"]50[/TD]
[TD]Resurs7[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Test10[/TD]
[TD]Owner10[/TD]
[TD="align: right"]2018-01-01[/TD]
[TD="align: right"]2018-12-31[/TD]
[TD="align: right"]1 000[/TD]
[TD]Team10[/TD]
[TD]Resurs10[/TD]
[TD="align: right"]50[/TD]
[TD]Resurs9[/TD]
[TD="align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: cke_show_border"]
<tbody>[TR]
[TD][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 126"]B[/TD]
[TD="width: 73"]C[/TD]
[TD="width: 73"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 147"]F[/TD]
[TD="width: 64"]G[/TD]
[TD="width: 64"]H[/TD]
[TD="width: 64"]I[/TD]
[TD="width: 64"]J[/TD]
[/TR]
[TR]
[TD="width: 64"]1[/TD]
[TD="width: 64"]Name[/TD]
[TD="width: 126"]Owner[/TD]
[TD="width: 73"]Start[/TD]
[TD="width: 73"]End[/TD]
[TD="width: 64"]Time (H)[/TD]
[TD="width: 147"]Team[/TD]
[TD="width: 64"]R1[/TD]
[TD="width: 64"]%[/TD]
[TD="width: 64"]R2[/TD]
[TD="width: 64"]%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Test1[/TD]
[TD]Owner1[/TD]
[TD="align: right"]2018-01-01[/TD]
[TD="align: right"]2018-12-31[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Test2[/TD]
[TD]Owner2[/TD]
[TD="align: right"]2018-01-01[/TD]
[TD="align: right"]2018-12-31[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Test3[/TD]
[TD]Owner3[/TD]
[TD="align: right"]2018-01-01[/TD]
[TD="align: right"]2018-12-31[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Test4[/TD]
[TD]Owner4[/TD]
[TD="align: right"]2018-01-01[/TD]
[TD="align: right"]2018-12-31[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Test5[/TD]
[TD]Owner5[/TD]
[TD="align: right"]2018-01-01[/TD]
[TD="align: right"]2018-12-31[/TD]
[TD="align: right"]500[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Test6[/TD]
[TD]Owner6[/TD]
[TD="align: right"]2018-01-01[/TD]
[TD="align: right"]2018-12-31[/TD]
[TD="align: right"]600[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Test7[/TD]
[TD]Owner7[/TD]
[TD="align: right"]2018-01-01[/TD]
[TD="align: right"]2018-12-31[/TD]
[TD="align: right"]700[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Test8[/TD]
[TD]Owner8[/TD]
[TD="align: right"]2018-01-01[/TD]
[TD="align: right"]2018-12-31[/TD]
[TD="align: right"]800[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Test9[/TD]
[TD]Owner9[/TD]
[TD="align: right"]2018-01-01[/TD]
[TD="align: right"]2018-12-31[/TD]
[TD="align: right"]900[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Test10[/TD]
[TD]Owner10[/TD]
[TD="align: right"]2018-01-01[/TD]
[TD="align: right"]2018-12-31[/TD]
[TD="align: right"]1 000[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to the board.
Are the col A values on sheet1 unique?

 

[RobertHamberg]

Hi & welcome to the board.
Are the col A values on sheet1 unique?

Values in col A are unique in both sheet1 and sheet2. I have a script that selects certain rows in sheet1 and copy them to sheet2, then a script that adds values to rows in sheet2. Now I need a script that copy those values from sheet2 back to sheet1 on matching rows

 

[Fluff]

How about

Sub CopyCols()

   Dim Cl As Range
   Dim Sht1 As Worksheet
   Dim Sht2 As Worksheet
   
   Set Sht1 = Sheets("Sheet1")
   Set Sht2 = Sheets("Sheet2")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Sht1.Range("A2", Sht1.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 5).Resize(, 5)
      Next Cl
      For Each Cl In Sht2.Range("A2", Sht2.Range("A" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 5).Resize(, 5).Value = .Item(Cl.Value).Value
      Next Cl
   End With

End Sub

 

[vds1]

Without loop

Sub CopyPaste()
    Dim LastRow     As Long
    Sheets("Sheet2").Select
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("F2").Formula = "=IFERROR(VLOOKUP($A2,Sheet1!$A:$J,COLUMN(),0),"""")"
    Range("F2").Copy
    With Range("F2:J" & LastRow)
        .PasteSpecial xlPasteFormulas
        .Copy
        .PasteSpecial xlPasteValues
    End With
End Sub

 

[Fluff]

Or a slightly simpler (and corrected, missing a = sign) version vds1 code

Sub CopyPaste()
    Dim LastRow     As Long
    With Sheets("Sheet2")
      LastRow = .Range("A" & Rows.Count).End(xlUp).Row
      With .Range("F2:J" & LastRow)
         .Formula = "=IFERROR(VLOOKUP($A2,Sheet1!$A:$J,COLUMN(),0),"""")"
         .Value = .Value
      End With
   End With
End Sub

 

[RobertHamberg]

Thanks alot for the help!

 

[Fluff]

Glad we could help.

 

[RobertHamberg]

I need help to re-write this script. I need it to do the following. Match value in column A in Sheet 1 with value in column A in Sheet 2, if match found I want the script to copy and replace the row on sheet 2 with the row from sheet 1.

I want it to work no matter the amount of columns and rows that contain data in sheet 1 and sheet 2.

Thanks in advance!

Sheet 1[TABLE="width: 500"]
<tbody>[TR]
[TD]
[TABLE="class: cke_show_border"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD="width: 73"]C
[/TD]
[TD="width: 73"]D
[/TD]
[TD="width: 64"]E
[/TD]
[TD="width: 147"]F
[/TD]
[TD="width: 64"]G
[/TD]
[TD="width: 64"]H
[/TD]
[TD="width: 64"]I
[/TD]
[TD="width: 64"]J
[/TD]
[/TR]
[TR]
[TD="width: 64"]1
[/TD]
[TD="width: 64"]Name
[/TD]
[TD="width: 126"]Owner
[/TD]
[TD="width: 73"]Start
[/TD]
[TD="width: 73"]End
[/TD]
[TD="width: 64"]Time (H)
[/TD]
[TD="width: 147"]Team
[/TD]
[TD="width: 64"]R1
[/TD]
[TD="width: 64"]%
[/TD]
[TD="width: 64"]R2
[/TD]
[TD="width: 64"]%
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Test2
[/TD]
[TD]Owner2
[/TD]
[TD="align: right"]2018-01-01
[/TD]
[TD="align: right"]2018-12-31
[/TD]
[TD="align: right"]200
[/TD]
[TD]Team2
[/TD]
[TD]Resurs2
[/TD]
[TD="align: right"]100
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2[TABLE="width: 500"]
<tbody>[TR]
[TD]
[TABLE="class: cke_show_border"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD="width: 73"]C
[/TD]
[TD="width: 73"]D
[/TD]
[TD="width: 64"]E
[/TD]
[TD="width: 147"]F
[/TD]
[TD="width: 64"]G
[/TD]
[TD="width: 64"]H
[/TD]
[TD="width: 64"]I
[/TD]
[TD="width: 64"]J
[/TD]
[/TR]
[TR]
[TD="width: 64"]1
[/TD]
[TD="width: 64"]Name
[/TD]
[TD="width: 126"]Owner
[/TD]
[TD="width: 73"]Start
[/TD]
[TD="width: 73"]End
[/TD]
[TD="width: 64"]Time (H)
[/TD]
[TD="width: 147"]Team
[/TD]
[TD="width: 64"]R1
[/TD]
[TD="width: 64"]%
[/TD]
[TD="width: 64"]R2
[/TD]
[TD="width: 64"]%
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Test1
[/TD]
[TD]Owner1
[/TD]
[TD="align: right"]2018-01-01
[/TD]
[TD="align: right"]2018-12-31
[/TD]
[TD="align: right"]100
[/TD]
[TD]Team2
[/TD]
[TD]Resurs2
[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]Resurs2[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Test2
[/TD]
[TD]Owner2

[/TD]
[TD="align: right"]2018-01-01
[/TD]
[TD="align: right"]2018-12-31
[/TD]
[TD="align: right"]200
[/TD]
[TD]Team2

[/TD]
[TD]Resurs6
[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]Resurs4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Test3
[/TD]
[TD]Owner3
[/TD]
[TD="align: right"]2018-01-01
[/TD]
[TD="align: right"]2018-12-31
[/TD]
[TD="align: right"]300
[/TD]
[TD]Team2
[/TD]
[TD]Resurs2
[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]Resurs1[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Test4
[/TD]
[TD]Owner4
[/TD]
[TD="align: right"]2018-01-01
[/TD]
[TD="align: right"]2018-12-31
[/TD]
[TD="align: right"]400
[/TD]
[TD]Team2
[/TD]
[TD]Resurs2
[/TD]
[TD="align: right"]75[/TD]
[TD]Resurs3[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Test5
[/TD]
[TD]Owner5
[/TD]
[TD="align: right"]2018-01-01
[/TD]
[TD="align: right"]2018-12-31
[/TD]
[TD="align: right"]500
[/TD]
[TD]Team25
[/TD]
[TD]Resurs2
[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]Resurs6[/TD]
[TD="align: right"]20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Maybe

Sub CopyPaste()
    Dim UsdRws As Long
    Dim UsdCols As Long
    With Sheets("Sheet2")
      UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
      UsdCols = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
      With .Range("B2", .Cells(UsdRws, UsdCols))
         .Formula = "=IFERROR(VLOOKUP($A2,Sheet1!$1:$" & UsdRws & ",COLUMN(),0),"""")"
         .Value = .Value
      End With
   End With
End Sub