VBA Looping to compare multiple values

Question

I have created a nested for loop to compare 3 different cell values within 2 sheets. The loop works fine when the data is small, but when I run on 5,000 rows its too slow and crashes excel. Any idea of how to run this more efficiently.

    Sub RowMatch()

Dim x As Integer

      ' Make sure we are in the right sheet
      Worksheets("Q416").Activate
      ' Set numrows = number of rows of data.
      NumRows = Range("C2", Range("C2").End(xlDown)).Rows.count
      ' find the reference range
      Worksheets("Q415").Activate
      NumRows2 = Range("C5", Range("C5").End(xlDown)).Rows.count
      Worksheets("Q416").Activate
      MsgBox ("Total # of Rows on this sheet = " & NumRows & " and " & NumRows2 & " in Ref Range")
      Range("A1").Select
      ' Establish "For" loop to loop "numrows" number of times.
      For x = 1 To NumRows
        'MsgBox NumRows2
        For y = 1 To NumRows2
        'MsgBox (ActiveCell.Offset(x, 0).Value & " & " & Worksheets("Q415").Cells(y + 1, 1))
        If ActiveCell.Offset(x, 0).Value = Worksheets("Q415").Cells(y + 1, 1).Value _
        And ActiveCell.Offset(x, 2).Value = Worksheets("Q415").Cells(y + 1, 3).Value Then
        If ActiveCell.Offset(x, 5).Value = Worksheets("Q415").Cells(y + 1, 6).Value Then
        'If NumRows(i).Value = ActiveCell.Offset(1, 0).Value Then
        ActiveCell.Offset(x, 10).Value = "Same"
        Else
        ActiveCell.Offset(x, 10).Value = ActiveCell.Offset(x, 5).Value - Worksheets("Q415").Cells(y + 1, 6).Value
        End If
        End If


        Next y
        Next x
End Sub

Death by ActiveCell! Yes, have a quick search on how to avoid using Select/Activate/ActiveCell — CallumDA, Mar 13 '17 at 20:33
You could try the .find method and match another column of each found row for the second match. Take a look at http://stackoverflow.com/questions/42768906/excel-macro-iterating-through-rows-and-combined-loop — John Muggins, Mar 13 '17 at 20:36
I guess the post @CallumDA is referring to is the following: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — Ralph, Mar 13 '17 at 20:37

score 1 · Answer 1 · edited May 08 '18 at 21:49

Reading and writing to cells is one of the slowest operations you can do in Excel VBA. Instead, you should place the values contained in the worksheets into arrays and work with them there, Here is an excellent reference: http://www.cpearson.com/excel/ArraysAndRanges.aspx. Use your NumRows variables and either a column letter or number to define the ranges that will consitute the arrays e.g:

myRange = Range("A1:C" & NumRows)
myArray = myRange.value

From the link to Chip Pearsons site:

Dim Arr() As Variant
Arr = Range("A1:B10")
Dim R As Long
Dim C As Long
For R = 1 To UBound(Arr, 1) ' First array dimension is rows.
    For C = 1 To UBound(Arr, 2) ' Second array dimension is columns.
        Debug.Print Arr(R, C)
    Next C
Next R

VBA Looping to compare multiple values

1 Answers1