PDA

View Full Version : [SOLVED] Copying entire row from Sheet1 to Sheet2 if values in multiple cells matches



squippe
01-30-2017, 07:22 AM
Please help!

I have new data in sheet 1 and old data in sheet 2 where I have added some comments too. I would like to copy the comments from sheet 2 to sheet 1 if the data matches between the sheets in two cells on a same row.

For example:

Sheet 1 contains the following data:

A1= red
B1 = 2


Sheet 2 contains the old data and my comment in C20:

A20= red
B20= 2
C20= "Match"

I need VBA to go through the matching criteria A20 & B20 from Sheet 1 and copy paste "Match" to C1 if both criteria (red and 2) is true.

Thank you for any help!

mana
01-31-2017, 04:01 AM
Option Explicit


Sub test()
Dim dic As Object
Dim ws1 As Worksheet, ws2 As Worksheet
Dim c As Range
Dim s As String

Set dic = CreateObject("scripting.dictionary")
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

For Each c In ws2.Range("a1", ws2.Range("a" & Rows.Count).End(xlUp))
dic(c.Value & vbTab & c.Offset(, 1).Value) = c.Offset(, 2).Value
Next

For Each c In ws1.Range("a1", ws1.Range("a" & Rows.Count).End(xlUp))
s = c.Value & vbTab & c.Offset(, 1).Value
If dic.exists(s) Then c.Offset(, 2).Value = dic(s)
Next


End Sub

squippe
01-31-2017, 05:06 AM
Thank you!