Most people use the .Find method of a range object or use functions like If Not IsError(Application.Match([Value to Search in the Range],[Range to Search],0)) to determine whether a certain value can be found in a range. Other methods can be found in here. But if you want to match more than one criteria, it gets a little more complicated.
For instance, I want to check whether a certain Person/Date pair is present in another worksheet then write that pair if not found in the said worksheet. Referring to the example below.
The first way I would think of is use the code below:
Option Explicit
Sub Payroll()
Dim i As Long, j As Long, Present As Long
Dim Total_rows_HoursWorked As Long
Dim Total_rows_DailyTimeRecord As Long
ThisWorkbook.Worksheets("Hours Worked").Cells(1, 1) = "Person"
ThisWorkbook.Worksheets("Hours Worked").Cells(1, 2) = "Date"
Total_rows_DailyTimeRecord = ThisWorkbook.Worksheets("Daily Time Record").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To Total_rows_DailyTimeRecord
Present = 0
Total_rows_HoursWorked = ThisWorkbook.Worksheets("Hours Worked").Range("A" & Rows.Count).End(xlUp).Row
For j = 2 To Total_rows_HoursWorked
If ThisWorkbook.Worksheets("Daily Time Record").Cells(i, 1) = ThisWorkbook.Worksheets("Hours Worked").Cells(j, 1) And _
ThisWorkbook.Worksheets("Daily Time Record").Cells(i, 2) = ThisWorkbook.Worksheets("Hours Worked").Cells(j, 2) Then
Present = 1
End If
Next j
If Present = 0 Then
ThisWorkbook.Worksheets("Hours Worked").Cells(Total_rows_HoursWorked + 1, 1) = ThisWorkbook.Worksheets("Daily Time Record").Cells(i, 1)
ThisWorkbook.Worksheets("Hours Worked").Cells(Total_rows_HoursWorked + 1, 2) = ThisWorkbook.Worksheets("Daily Time Record").Cells(i, 2)
End If
Next i
End Sub
The output would be below:
But the issue with this is that it is very inefficient which will result it to run through more rows that are needed and it will be very slow especially if size of worksheets increase.
I can opt to use Arrays as well in order to speed up instead of looping through each row in a worksheet but it would still have to go through more rows than needed in order to find a match.
Another method that can be used is .Autofilter to attempt to look for matches in a certain range to minimize the looping to only those that match a certain criteria. But there is also some lag to this method but is generally faster than the first method.
What is a better way or best way of doing such tasks?
Edit: It is not just finding the unique values but also similar to finding all values that match a certain set of criteria such as the example below:
Charles William's blog made it in a way that the ranges are resized for Application.Match and .Find but shows that Variant Array does the best but does that mean the only option is to create a nested loop and loop through each one by one, but using an array?
