0
\$\begingroup\$

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
\$\endgroup\$
4
  • \$\begingroup\$ 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? \$\endgroup\$
    – PeterT
    Mar 10, 2022 at 17:01
  • \$\begingroup\$ Cross-posted on Stack Overflow \$\endgroup\$
    – Mast
    Mar 11, 2022 at 16:05
  • \$\begingroup\$ Putting these formulas in to cells will be faster than setting them via VBA. \$\endgroup\$
    – HackSlash
    Mar 11, 2022 at 18:03
  • \$\begingroup\$ 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. \$\endgroup\$ Mar 14, 2022 at 18:24

1 Answer 1

1
\$\begingroup\$

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
 
\$\endgroup\$
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.