Skip to main content
Code Review

Return to Question

make title describe what code does instead of goals - refer to help center section "Titling your question" on https://codereview.stackexchange.com/help/how-to-ask
Source Link
Sᴀᴍ Onᴇᴌᴀ ♦
Sᴀᴍ Onᴇᴌᴀ ♦
  • 29.6k
  • 16
  • 46
  • 203

This code imports importing excel data by matching and performing lookups. Is there a way to increase performance? It uses Vlookup and locked data ranges to iterate

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

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

This code imports data by matching and performing lookups. Is there a way to increase performance? It uses Vlookup and locked data ranges to iterate

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

importing excel data by matching and performing lookups

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
Fixed spelling and grammar.
Source Link
pacmaninbw ♦
pacmaninbw ♦
  • 26.1k
  • 13
  • 47
  • 114

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 isare 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

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 is 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

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
Fixed spelling and grammar.
Source Link
pacmaninbw ♦
pacmaninbw ♦
  • 26.1k
  • 13
  • 47
  • 114

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 hteresthere is 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

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 hteres 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

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 is 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
Source Link
Bubbybristor
Bubbybristor
  • 9
  • 3