CODEWITHSUNDEEP
excelvbaoffice365
deLa
deLa

Reputation: 99

Searching for matches: Array vs Individual

Here's the code in question:

Dim rr23WS As Worksheet, rrCell As Range
Dim rrCheck As Range
Dim r As Long
Dim rrMatch
Dim openWKBK as String

openWKBK = "X:\Resulting\Test.xlsx"
Workbooks.Open (openWKBK)
Set rr23WS = Workbooks("Test.xlsx").Sheets("January")
Set rrCheck = rr23WS.Columns(1)


For r = 1 To 4
                
    For Each rrCell In Worksheets("RACK " & r).Range("C6:N13").Cells
        
        rrCell.Borders.Color = RGB(0, 0, 0)
        rrCell.Borders.Weight = xlThin
        
        rrMatch = Application.Match(rrCell, rrCheck, 0)               'search Col A of Test.xlsx for matches
                
            If Not IsError(rrMatch) Then                 

                rrCell.Borders.Color = RGB(0, 0, 192)     'if match found, apply border to cell located in the range of ("C6:N13")
                rrCell.Borders.Weight = xlThick
                
            End If
    
    Next rrCell
    
Next r
End Sub

Everything works fine but, every time the button is clicked it's always searching Test.xlsx wkbk for ~350-400 matches, but the searchable range on Test.xlsx is growing rapidly and I'm starting to notice the macro slow down a bit (takes about 10seconds which I understand is not a long time at all, I'm just thinking for the future as the searchable range continues to increase).

From my understanding of how my code is actually working, it seems the macro is constantly jumping back and forth between the 2 workbooks looking for matches. If that's correct, it just sounds inefficient.

I'm wondering if it would make sense to set the entire range of ("C6:N13") for all 4 worksheets to an array so it can search for everything at once and not jump between the 2 workbooks.

With my current, inexperienced, understanding of how excel performs step-by-step execution, I would think this would help speed it up.

Which is the lesser evil:

  1. searching a large range for multiple matches in an array of ~400 values?
  2. searching a large range for multiple matches for ~400 cells individually?

Upvotes: 0

Views: 72

Answers (1)

Tim Williams
Tim Williams

Reputation: 166331

Match against a worksheet is typically very fast - more likely it's the formatting of the cells which is slow. You can set the cell border to black for C6:N13 before entering the loop over that range: no need to set each cell individually.

Likewise set all the matched cells for any given sheet in one operation not individually

Sub lookitup()
    
    Const WB_PATH As String = "X:\Resulting\Test.xlsx" 'use constants for fixed values
    Const RNG_RACK As String = "C6:N13"
    
    Dim rr23WS As Worksheet, rrCell As Range, rngRack As Range
    Dim rrCheck As Range, rngMatched As Range
    Dim r As Long
    
    Set rr23WS = Workbooks.Open(WB_PATH).Sheets("January") 'get return value from Open()
    Set rrCheck = rr23WS.Columns(1)
    
    For r = 1 To 4
        'always qualify worksheets with a workbook object
        Set rngRack = ThisWorkbook.Worksheets("RACK " & r).Range(RNG_RACK)
        
        'reset to defaults...
        rngRack.Borders.Color = RGB(0, 0, 0)
        rngRack.Borders.Weight = xlThin
        
        Set rngMatched = Nothing
        For Each rrCell In rngRack.Cells
            If Not IsError(Application.Match(rrCell, rrCheck, 0)) Then
                'collect cell for later formatting
                If rngMatched Is Nothing Then
                    Set rngMatched = rrCell
                Else
                    Set rngMatched = Application.Union(rrCell, rngMatched)
                End If
            End If
        Next rrCell
        
        'format matched cells if any
        If Not rngMatched Is Nothing Then
            With rngMatched.Borders
                .Color = RGB(0, 0, 192)
                .Weight = xlThick
            End With
        End If
    Next r 'next sheet

    rr23WS.Parent.Close False 'close lookup workbook 

End Sub

Upvotes: 1

Related Questions