CODEWITHSUNDEEP
excelloopsvba
AbeeCrombie
AbeeCrombie

Reputation: 607

VBA Looping to compare multiple values

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

Upvotes: 0

Views: 1591

Answers (1)

Absinthe
Absinthe

Reputation: 3391

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

Upvotes: 1

Related Questions