Speed up Macro

[p45cal]

how would I modify it on the test2 file where I want to compare Column B in both sheets, then import the values from Column A in sheet 1 into Column D of sheet2.

VLookUp is designed to look up a vaue in the leftmost column of a range and return the value in one of the columns to the right of it. In this case it's easier to use Index/Match (Vlookup can be used to lookup to the left but it's convoluted to do so and I've never made it work in vba, only on a sheet). This code is designed to work on your Test2.xlsm file:

Sub GetValues5()
Set rng1 = Range(Worksheets("Sheet1").Range("B2"), Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp))
Set rng2a = Range(Worksheets("Sheet2").Range("B2"), Worksheets("Sheet2").Range("B" & Rows.Count).End(xlUp))
Set rng2b = rng2a.Offset(, -1)
'---------------------------
'either:
cc = Application.Index(rng2b, Application.Match(rng1, rng2a, 0))

'or (if you've got more than 1 column to process you can use the same index):
'dd = Application.Match(rng1, rng2a, 0)
'cc = Application.Index(rng2b, dd)
'---------------------------
For i = 1 To UBound(cc)
  If IsError(cc(i, 1)) Then cc(i, 1) = Empty
Next i
rng1.Offset(, 2).Value = cc
End Sub

[p45cal]

I'd have thought that building the dictionary and looking up indexes would have added more overhead

Paul, I agree, one would expect that, but I've long since expected the unexpected with Excel and often test something like this to try to confirm the expected. [Actually, this isn't confined to Excel, but to life in general; so many times people (politicians included/especially) make a statement which they try and make out is blinking obvious, but when you dig deeper you find it's not so obvious, and is often downright wrong. People call me cynical, but I won't change as this sort of thing happens only too often]
Anyway, did you look at my testing file in msg#18 and get similar results?

[Fluff]

I got similar times although I was getting times of ~7.8 for TEST.
But with this version I was getting ~6.4

Sub TEST()
Dim r1 As Range, r2 As Range
Dim ary12 As Variant, ary22 As Variant, ary1 As Variant, ary2 As Variant
Dim i As Long, m As Long
StartTime = Timer
    
Set r1 = Worksheets("sheet1").Cells(1, 1).CurrentRegion.Resize(, 2)
Set r2 = Worksheets("sheet2").Cells(1, 1).CurrentRegion
    
ary12 = r1.Value
ary22 = r2.Value
'
'ary1 = Application.WorksheetFunction.Transpose(r1.Columns(1))
'ary2 = Application.WorksheetFunction.Transpose(r2.Columns(1))
    
    
For i = LBound(ary12) To UBound(ary12)
  For j = LBound(ary22) To UBound(ary22)
      If ary12(i, 1) = ary22(j, 1) Then
         ary12(i, 2) = ary22(j, 2)
         Exit For
   End If
   Next j
Next i
Debug.Print "TEST" & vbTab & Timer - StartTime
    Sheet1.Range("B1").Resize(UBound(ary12)).Value = Application.Index(ary12, 0, 2)
'r1.Value = ary12 'commented out because I didn't want possibly to mess column A
End Sub

Nested loops through arrays seems faster than using Application.Match

[framcc06]

Thanks p45cal, I did think I needed to use Offset somewhere but just didn't know where to stick it in the code.

Once again thanks to everyone who have helped with their suggestions.

Fra