importing excel data by matching and performing lookups

Question

This code imports data from a separate sheet and matches it based on Column E's data, it does this for every cell then offsets to the next one (622*6) times. If there are any substitutions for vlookup that are an order of magnitude faster, that is probably the ideal solution.

I have about 622 rows of data to match across 6 columns. I feel as though the "Application" handle for worksheet functions is slowing this script down.

Sub Questionnaire_to_Ventilation()
'
' Questionnaire_to_Ventilation Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Application.ScreenUpdating = False
    Sheets("Ventilation").Select
    Dim LRow As Long
    LRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "E").End(xlUp).Row
    For i = 0 To LRow
        For col = 8 To 13
            Sheets("Ventilation").Range("Y10").Offset(i, col - 8) = Application.IfError(Application.VLookup _
            (Sheets("Ventilation").Range("E10").Offset(i, 0), Sheets("Scheduling Questionnaire").Range("$B$11:$N$3337"), col, False), "")
        Next col
    Next i
Range("Y10").Select
Application.ScreenUpdating = True
End Sub

Answer 1

There is usually some performance benefit to setting Ranges that remain constant external to a loop rather than re-generating them for each iteration. The code below extracts those elements from the loop. This may get some of the performance improvement you are looking for.

Sub Questionnaire_to_Ventilation()
'
' Questionnaire_to_Ventilation Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Application.ScreenUpdating = False
    Dim wkshtVentilation As Worksheet
    Set wkshtVentilation = Sheets("Ventilation")
    
    Dim rangeY10 As Range
    Set rangeY10 = wkshtVentilation.Range("Y10")
    
    Dim rangeE10 As Range
    Set rangeE10 = wkshtVentilation.Range("E10")
    
    Dim questionaireRange As Range
    Set questionaireRange = Sheets("Scheduling Questionnaire").Range("$B$11:$N$3337")
    
    Dim LRow As Long
    LRow = wkshtVentilation.Cells(wkshtVentilation.Rows.Count, "E").End(xlUp).Row
    For i = 0 To LRow
        For col = 8 To 13
            rangeY10.Offset(i, col - 8) _
                = Application.IfError( _
                    Application.VLookup(rangeE10.Offset(i, 0), questionaireRange, col, False), _
                    "")
        Next col
    Next i
rangeY10.Select
Application.ScreenUpdating = True
End Sub