Excel VBA - Find matching values in columns and return value in another column

Question

I would like to determine if the values in Column H of Workbook 1 and Column A of Workbook 2 match, then return “Y” in the corresponding rows of Column S of Workbook 1 for matches, “N” for non-matches.

However, the duration required to run my current code is extremely long (> 15 minutes), is there a way to shorten it?

Here is my current VBA code:

Dim j, LastRow As Long
Dim answer, found As Range

LastRow = Workbooks("1.xlsx").Sheets("AA").Range("H" & Rows.Count).End(xlUp).Row

For j = 1 To LastRow
answer = Workbooks("1.xlsx").Sheets("AA").Range("H" & j).Value

Set found = Workbooks("2.xlsx").Sheets("BB").Columns("A:A").Find(what:=answer)

If found Is Nothing Then
    Workbooks("1.xlsx").Sheets("AA").Range("S" & j).Value = "N"
Else
    Workbooks("1.xlsx").Sheets("AA").Range("S" & j).Value = "Y"
End If

Next j

Answer 1

Please, try the next code:

Sub matchData()
  Dim ws As Worksheet, ws2 As Worksheet, j, LastRow As Long, arrH, arrFin
  Dim answer, found As Range
  
  Set ws = Workbooks("1.xlsx").Sheets("AA")
  Set ws2 = Workbooks("2.xlsx").Sheets("BB")
  LastRow = ws.Range("H" & rows.count).End(xlUp).row
  
  arrH = ws.Range("H1:H" & LastRow).value 'put the range in an array to make the iteration faster
  ReDim arrFin(1 To UBound(arrH), 1 To 1) 'redim the array to receive the comparison result
  For j = 1 To UBound(arrH)
        answer = ws.Range("H" & j).value
        
        Set found = ws2.Columns("A:A").Find(what:=answer) 'faster than iteration even in an array...
        
        If found Is Nothing Then
            arrFin(j, 1) = "N"  'fill the array element with the appropriate string
        Else
            arrFin(j, 1) = "Y"  'fill the array element with the appropriate string
        End If
  Next j
  ws.Range("S1").Resize(UBound(arrFin), 1).value = arrFin 'drop the array content at once (very fast)
End Sub

I would like to receive some feedback about its duration...