zkristen
04-15-2018, 04:15 PM
Hi! I have looked everywhere for help with a macro and am only halfway there. I am new here, so if I'm missing something, let me know! Thanks!
The main problem is that I have to sets of data. Each row in sheet2 should have a match in sheet1, but sometimes there is a row that is missing a match. I am matching two criteria, and sometimes there are duplicates. I need to know when there is a row in sheet2 without a match in sheet1.
Here is what I am thinking and what I have tried:
I have two sheets in the same workbook. If the values in columns D and F of Sheet 2 match the values in columns C and E (respectively) of Sheet 1, I want to copy the row from Sheet 1 (columns A to H are being used) to Sheet 2 starting in Column H of Sheet 2. The extra tricky part is there could be multiple matches. I want to copy each time there is a match without repeating. I want to do this for every row in Sheet 2. Depending on the data, there could be 100 rows or 10,000. I want to keep the macro the same and be able to use it with different data sets.
Here is the code I have tried. It starts replacing in column D instead of column H, and doesn't copy duplicates. It also only matches for one criteria.
Option Explicit
Sub cctest()
Dim NewDataRng As Range 'For Sheet1
Dim Cel As Range 'For Sheet1
Dim OldDataRng As Range 'For Sheet2
Dim MatchingValueCell As Range 'For Sheet2
Dim LastRow As Long
With Sheets("Sheet1")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set NewDataRng = .Range("C2:C" & CStr(LastRow))
End With
With Sheets("Sheet2")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set OldDataRng = .Range("D2:D" & CStr(LastRow))
End With
For Each Cel In NewDataRng
Set MatchingValueCell = OldDataRng.Find(What:=Cel.Value, _
After:=OldDataRng.Cells(OldDataRng.Cells.Count))
If Not MatchingValueCell Is Nothing Then _
Cel.Resize(1, 8).Copy MatchingValueCell
Next Cel
End Sub
Here's a sample file:
22029
The main problem is that I have to sets of data. Each row in sheet2 should have a match in sheet1, but sometimes there is a row that is missing a match. I am matching two criteria, and sometimes there are duplicates. I need to know when there is a row in sheet2 without a match in sheet1.
Here is what I am thinking and what I have tried:
I have two sheets in the same workbook. If the values in columns D and F of Sheet 2 match the values in columns C and E (respectively) of Sheet 1, I want to copy the row from Sheet 1 (columns A to H are being used) to Sheet 2 starting in Column H of Sheet 2. The extra tricky part is there could be multiple matches. I want to copy each time there is a match without repeating. I want to do this for every row in Sheet 2. Depending on the data, there could be 100 rows or 10,000. I want to keep the macro the same and be able to use it with different data sets.
Here is the code I have tried. It starts replacing in column D instead of column H, and doesn't copy duplicates. It also only matches for one criteria.
Option Explicit
Sub cctest()
Dim NewDataRng As Range 'For Sheet1
Dim Cel As Range 'For Sheet1
Dim OldDataRng As Range 'For Sheet2
Dim MatchingValueCell As Range 'For Sheet2
Dim LastRow As Long
With Sheets("Sheet1")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set NewDataRng = .Range("C2:C" & CStr(LastRow))
End With
With Sheets("Sheet2")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set OldDataRng = .Range("D2:D" & CStr(LastRow))
End With
For Each Cel In NewDataRng
Set MatchingValueCell = OldDataRng.Find(What:=Cel.Value, _
After:=OldDataRng.Cells(OldDataRng.Cells.Count))
If Not MatchingValueCell Is Nothing Then _
Cel.Resize(1, 8).Copy MatchingValueCell
Next Cel
End Sub
Here's a sample file:
22029