There’s a reason I named this blog the way I did. The first post is a good example of how a dummy does it. Turns out I did the opposite of what the asker needed. He wanted to find non-matching pairs, and I gave him matching pairs. So I don’t reexplain from scratch here, but I’m going to give an updated macro and explain the differences.
Scope of Application
This post references Microsoft Excel 2013 running on a Windows 8.1 machine. I’ll also be referencing Stackoverflow as experienced through Firefox 28.0 running in Sandboxie 4.08.
This one’s on me. I read the question once, started looking at the sample data, then forgot what the ultimate goal was because I was caught up in the minutiae of making things work. Granted, this was a casual response to an online question, but this is about par for the course in programming. Sometimes the requirements change, many times they were ill-defined in the first place, you have to constantly be on the lookout for scope creep, and other times, it turns out that I’m just kind of a dummy. So here’s a macro that does what the user actually asked for:
Sub TakeThree() Dim rowCount1 As Long Dim rowCount2 As Long rowCount1 = ThisWorkbook.Sheets(1).Range("B20").SpecialCells(xlCellTypeLastCell).Row rowCount2 = ThisWorkbook.Sheets(2).Range("C2").SpecialCells(xlCellTypeLastCell).Row Dim rng1 As Range Dim rng2 As Range Set rng1 = ThisWorkbook.Sheets(1).Range("B20:B" & rowCount1) Set rng2 = ThisWorkbook.Sheets(2).Range("C2:C" & rowCount2) Dim currentRow As Long currentRow = 2 For Each cell In rng1.Cells For Each cell2 In rng2.Cells If cell2.Value = cell.Value And cell2.Offset(0, -1).Value = cell.Offset(0, 5).Value And cell2.Offset(0, -2).Value = cell.Offset(0, 2).Value Then GoTo NextIteration End If Next cell2 ThisWorkbook.Sheets(1).Rows(cell.Row).Copy Destination:=ThisWorkbook.Sheets(3).Range("A" & currentRow) currentRow = currentRow + 1 NextIteration: Next cell End Sub
What’s the Difference?
We really haven’t changed much. We just moved the lines that copy and paste the row from sheet 1 and the currentRow increment outside of the loop through sheet 2. Why does that make it work? As I’ve said before (I think), the biggest part of coding isn’t so much about knowing how to read and write the language, it’s knowing how to work the logic.
Let’s frame the requirements one more time to help us understand how we got here. The asker wanted to compare data in 3 cells on sheet 1 to data in 3 cells on sheet 2. If the cells on sheet 1 do not match any of the corresponding cells in sheet 2, they should be copied to sheet 3. To be sure that each set of 3 cells from sheet 1 doesn’t match any of the corresponding cells in sheet 2, our only course of action is to compare each set of cells on sheet 1 to every set of cells on sheet 2–unless we get a match. As soon as the cells on sheet 1 match some cells on sheet 2, we don’t have to keep going with the comparison and can move to the next set of cells on sheet 1. But unless we get a match, we can’t copy anything from sheet 1 to sheet 3 until we’re sure we’ve exhausted every option.
So now look at what the loops are doing. The first one, “For Each cell in rng1.Cells,” is going to loop through every cell in sheet 1, as we discussed. It immediately starts another loop that will check the values at this spot in sheet 1 against a set of cells in sheet 2. If the cells from sheet 2 match the cells from sheet 1, the code jumps to the next iteration–completely bypassing the step that copies a row from sheet 1 to sheet 3. However, if the code gets through every single set of cells in sheet 2 without finding a match, then it leaves that loop. There waiting on the other side is the code that says, “Oh. Hey. Couldn’t find a match on the other sheet, and now you want me to give you a ride to sheet 3? I guess that’s cool.”
Hopefully that’s clear enough. Cuz that’s all I’ve got.
Love and kisses,