I want to write a macro to compare two columns on two separate sheets.
I want to search values in a column against a master list of valid entries for values from another column. The idea is that if a value on the first list does not appear on the second list, then that value is invalid. If it is, I want to copy the entire row for that invalid value and paste it onto another sheet containing a list of invalid entries along for a manual review.
I'm pretty sure I have to create 2 arrays containing these columns and I need to use 2 nested for loops. I've always struggled with loops and I'm new to VBA and need some help. This is what I have so far.
I want to search values in a column against a master list of valid entries for values from another column. The idea is that if a value on the first list does not appear on the second list, then that value is invalid. If it is, I want to copy the entire row for that invalid value and paste it onto another sheet containing a list of invalid entries along for a manual review.
I'm pretty sure I have to create 2 arrays containing these columns and I need to use 2 nested for loops. I've always struggled with loops and I'm new to VBA and need some help. This is what I have so far.
Code:
Sub Find_Invalid_Entries()
Dim r1 ' last row of the list with survey results
Dim r2 ' last row of the distribution list
Dim Range1 As Range
Dim Range2 As Range
Dim shtC As Worksheet
Dim shtB As Worksheet
Dim C As Integer 'to store the column number of column C in "Survey Results (Raw)"
Dim B As Integer 'to store the column number of column B in "Distribution List Check"
Set shtC = Sheets("Distribution List Check") 'storing the sheets...
Set shtB = Sheets("Survey Results (Raw)")
shtB.Activate 'no matter you are in the workbook, always run from the sheet B
r1 = Range("C2").End(xlDown).Row 'the last row of the list with the survey results
Set disRange1 = Range(Cells(1, 2), Cells(r1, 2)) 'here need to change the 2 for 1 if you do not want headers
C = 3 'column C and B, just the numbers
B = 2
shtC.Activate 'go to sheet B
r2 = Range("B2").End(xlDown).Row
Set disRange2 = Range(Cells(1, 2), Cells(r2, 2))
' Thinking I need to put nested for loops here
End Sub