Is there a faster way to compare data between dynamic arrays in VBA? -

i have below code working successfully, has run on 2 arrays of 130k+ rows each. current run time on full data set around 24 minutes , having added count @ 1 point, looping 9.8 billion times.

i have read through articles on using match, vlookup , seem suggest iterative loop (as have used) fastest method, have been unable understand how other methods work dynamic arrays , hence test appropriately.

is able tell me if there faster way complete activity, , if so, demonstrate how?

sub testvlookuparray()     dim psorg1() variant     dim psorg1tot variant     dim psorg1rt variant     dim psorg2() variant     dim psorg2tot variant     dim psorg2rt variant      sheets("sheet1").select     psorg2rt = application.counta(range("a:a"))     psorg2tot = "a1:b" & psorg2rt     psorg2 = range(psorg2tot) ' psorg2 allocated array      sheets("sheet2").select     psorg1rt = application.counta(range("a:a"))     psorg1tot = "a1:b" & psorg1rt     psorg1 = range(psorg1tot) ' psorg1 allocated array      = 2 ' increment org values in psorg1              finish = "no"         b = 1 ' increment org values in psorg2                     if psorg1(a, 1) = psorg2(b, 1)                 psorg1(a, 2) = psorg2(b, 2)                 finish = "true"             elseif b = psorg2rt                 psorg1(a, 2) = "null"                 finish = "true"             end if             b = b + 1         loop until finish = "true"         = + 1     loop until = psorg1rt + 1      sheets("sheet2").select     set destination = range("a1")     destination.resize(ubound(psorg1, 1), ubound(psorg1, 2)).value = psorg1  end sub 

i concur scripting.dictionary method.

this procedure makes use of scripting.dictionsry. need go vbe's tools ► references , add reference microsoft scripting runtime.

sub testvlookuparray()     dim psorg1 variant, psorg2 variant     dim long, b long     dim dpsorg2 new scripting.dictionary      dpsorg2.comparemode = textcompare      debug.print timer      sheets("sheet1")         = .cells(rows.count, 1).end(xlup).row         psorg2 = .cells(1, 1).resize(a, 2).value2 ' psorg2 allocated array         b = lbound(psorg2, 1) ubound(psorg2, 1)             dpsorg2.item(psorg2(b, 1)) = psorg2(b, 2)         next b     end      sheets("sheet2")         = .cells(rows.count, 1).end(xlup).row         psorg1 = .cells(1, 1).resize(a, 2).value2 ' psorg1 allocated array     end      debug.print dpsorg2.count     debug.print lbound(psorg2, 1) & ":" & ubound(psorg2, 1)     debug.print lbound(psorg2, 2) & ":" & ubound(psorg2, 2)     debug.print lbound(psorg1, 1) & ":" & ubound(psorg1, 1)     debug.print lbound(psorg1, 2) & ":" & ubound(psorg1, 2)      b = lbound(psorg1, 1) ubound(psorg1, 1)         if dpsorg2.exists(psorg1(b, 1))             psorg1(b, 2) = dpsorg2.item(psorg1(b, 1))         else             psorg1(b, 2) = "null"         end if     next b       sheets("sheet2")         .cells(1, 1).resize(ubound(psorg1, 1), ubound(psorg1, 2)) = psorg1     end      debug.print timer  end sub 

fwiw, sample data of 110k rows on sheet1 , 95k rows in sheet2 ran in 20 minutes, 40 seconds original code. above took 1.72 seconds on same data.