Optimizing Excel Code

[yuqiaoyan]

Hi,

I have written an excel macro to cross reference data from 1 worksheet
against another, but the code runs slowly. I'm hoping someone can help
me with:

(1) Suggestions on how to improve the performance of the code below.
Should I use an array to store the data first then compare?

Context: There are 2 main sheets I'm working on. The ActiveSheet and
the Temp Data sheet. In the ActiveSheet there are 2 columns I'm
working on. Column 14 called "Delete," which contains either 1 or 0.
The variable Delete_Col is 14. The other main column is Cells(Row,20)
which contains strings. Basically, if column 14 in the active sheet
has a 1, I want to compare Cells(row,20) to Cell(i,12) in the Temp
Data sheet. If the strings are equal, then put a 1 in Cell(i,13). If
not, place a 0.

*Note - Prior to this code, I also sort the ActiveSheet Delete column
largest to smallest so all the "1" are at the Top.

Do While Cells(Row, Delete_Col) = 1
For i = 2 To UsedRows Step 1
If Cells(Row, 20) = Worksheets("Temp Data").Cells(i, 12)
Then
Worksheets("Temp Data").Cells(i, 13) = 1
Exit For
ElseIf Worksheets("Temp Data").Cells(i, 13) <> 1 Then
Worksheets("Temp Data").Cells(i, 13) = 0
End If
Next i

If i > UsedRows Then
MsgBox ("Could Not Find All")
AdOffset = AdOffset + 1
End If
Row = Row + 1
Loop

(2) Can you point me to a website that provides some guidance on how
to think about improving performance? I want to better understand why
A is better than B?

Thanks
QiaoYan

 

[Bob Umlas]

As a first step, precede your code with
Application.Calculation = xlcalculationmanual
and end with
Application.Calculation = xlcalculationautomatic
Try again, you may be surprised.

 

[Guest]

In addition to turning off automatic calculation, turn off automatic screen
updating. That is, let the code run in the background without showing you
what it's doing.

Application.ScreenUpdating = False
' Your code here...
Application.ScreenUpdating = True

This should probably be saved until after you're confident the code is
performing correcty, but it can speed up code dramatically. This is
particularly true when working with multiple sheets.

HTH,
Pflugs