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