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
value
(each row in column E, starting at row 10) to search a range. BecauseVLOOKUP
matches thevalue
given to the first column in the range (assumes that ALL values in that first column are unique), the sub then grabs ALL the columns in that same row. The inner loop is not using different lookupvalues
, it's always using the same one so it will always find the same row, just return a different column. Is this the intended result? \$\endgroup\$