MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


Posted by Qroozn on January 09, 2002 8:42 PM

at the start put
application.screenupdating = false

at the end
application.screenupdating = True

see if it makes a dif

Posted by gerlemph on January 10, 2002 8:35 AM

I can't figure out exactly what you're trying to do, but you shouldn't activate cells in a loop- there's never a need for that - and see if it's possible to work with arrays or Dictionaries (include Microsoft Scripting Runtime) instead of ranges. If you describe it in more detail I can suggest more...