Any way to speed up this macro for large sheets?
Posted by Aron on January 09, 2002 8:14 PM
Is there any way to speed this up? For each cell, it cycles through an entire list to find a match; the list will be thousands of rows long. It's kinda slow. There are 5 matching criteria, and only three are sorted (equally). Any ideas?
Sub addData()
Let dr = 2
'changeDate
Range("AF1:AM1").Copy Cells(1, 40)
For Each c In Sheets("Desired outcome").Range("InputRange").Cells
c.Activate
For dr = 2 To 290 '290 will be 5,000+
Let ar = ActiveCell.Row
Let ac = ActiveCell.Column
'Check for matches:
If Cells(ar, 1) = Cells(dr, 32) And Cells(ar, 2) = Cells(dr, 33) And Cells(ar, 3) = Cells(dr, 34) And Cells(ar, 4) = Cells(dr, 35) And Cells(ar, 5) = Cells(dr, 36) And Cells(1, ac) Like "*" & Cells(dr, 38) & "*" Then
ActiveCell.Value = Cells(dr, 39)
Range(Cells(dr, 32), Cells(dr, 39)).Copy Cells(dr, 40)
Range(Cells(dr, 32), Cells(dr, 39)).Clear
Sheets("Desired outcome").Range("childData").Sort Key1:=Range("AF2"), Order1:=xlAscending, Key2:=Range("AH2" _
), Order2:=xlAscending, Key3:=Range("AG2"), Order3:=xlAscending, Header _
:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Exit For
End If
Next
Next
End Sub