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

What this sub appears to do is to use the same lookup value (each row in column E, starting at row 10) to search a range. Because VLOOKUP matches the value 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 lookup values, 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? — PeterT
– PeterT, Commented Mar 10, 2022 at 17:01
Putting these formulas in to cells will be faster than setting them via VBA. — HackSlash
– HackSlash, Commented Mar 11, 2022 at 18:03
One option could be caching the lookup values into a dictionary, then use that to speed up the lookup. You could store the key as a string, and store either an array or collection as the value. — Ryan Wildry
– Ryan Wildry, Commented Mar 14, 2022 at 18:24

BZngr · Accepted Answer · 2022-03-10 00:16:26Z

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

A different forum user submitted this script, but thanks for your help! stackoverflow.com/a/71416247/14271103 — Bubbybristor
– Bubbybristor, Commented Mar 10, 2022 at 17:53

Stack Exchange Network

importing excel data by matching and performing lookups

1 Answer 1

You must log in to answer this question.

Hot Network Questions

importing excel data by matching and performing lookups

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions