Calling vlookup/find/match? in VBA to search for column A values in column C, exit sub if not found

[lonesome_road_toad]

Hi all,

I have 2 columns of data that I want to ensure contain the same values every time I run the macro. The IDs in the columns come from 2 different data files that the user selects and I want to create a check so people don't accidentally mix and match the wrong files without realizing. The order of the IDs in the 2 columns is different but both columns should contain the same IDs, and if not, I want to build a routine that would call my Clear_Contents macro, display an error message box and then exit sub. I have tried calling vlookup in a For Each loop through Application.WorkbookFunction but it keeps throwing an error every time I run it. Am I missing an easy solution here? Thanks!

 

[offthelip]

you haven't specified which columns you want to compare, so I have assume column A and column H on sheet1 . I have used a dictionary approiace which is very fast:

Sub dictionary()
 ' this does an index match by using the dictionary object
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   tt = Timer()
   
   Set Dic = CreateObject("Scripting.dictionary")
   With Sheets("sheet1")
     LastRowA = .Cells(Rows.Count, "A").End(xlUp).Row
      Ary = .Range(.Cells(1, 1), .Cells(LastRowA, 1)).Value2   ' first column
     LastRowh = .Cells(Rows.Count, "H").End(xlUp).Row
      bry = .Range(.Cells(1, 8), .Cells(LastRowh, 8)).Value2   ' second column
   For i = 1 To UBound(Ary)
      Dic(Ary(i, 1)) = i
   Next i
   For i = 1 To UBound(bry)
   If (Dic.Exists(bry(i, 1))) Then
    Ary(Dic(bry(i, 1)), 1) = 0
    Else
    MsgBox (bry(i, 1) & " does not exist column A")
   End If
   Next i
   For i = 1 To UBound(Ary)
      If Ary(i, 1) <> 0 Then
      MsgBox (Ary(i, 1) & " does not exist column h")
      End If
   Next i
   End With
   End Sub

 

[lonesome_road_toad]

you haven't specified which columns you want to compare, so I have assume column A and column H on sheet1 . I have used a dictionary approiace which is very fast:

Sub dictionary()
 ' this does an index match by using the dictionary object
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   tt = Timer()
  
   Set Dic = CreateObject("Scripting.dictionary")
   With Sheets("sheet1")
     LastRowA = .Cells(Rows.Count, "A").End(xlUp).Row
      Ary = .Range(.Cells(1, 1), .Cells(LastRowA, 1)).Value2   ' first column
     LastRowh = .Cells(Rows.Count, "H").End(xlUp).Row
      bry = .Range(.Cells(1, 8), .Cells(LastRowh, 8)).Value2   ' second column
   For i = 1 To UBound(Ary)
      Dic(Ary(i, 1)) = i
   Next i
   For i = 1 To UBound(bry)
   If (Dic.Exists(bry(i, 1))) Then
    Ary(Dic(bry(i, 1)), 1) = 0
    Else
    MsgBox (bry(i, 1) & " does not exist column A")
   End If
   Next i
   For i = 1 To UBound(Ary)
      If Ary(i, 1) <> 0 Then
      MsgBox (Ary(i, 1) & " does not exist column h")
      End If
   Next i
   End With
   End Sub

Thank you so much! I was able to fit this to work as I needed, I've never used an index match and will definitely keep this in mind for future macros!