Optimizing Excel Code
As a first step, precede your code with
Application.Calculation = xlcalculationmanual
and end with
Application.Calculation = xlcalculationautomatic
Try again, you may be surprised.
wrote in message
oups.com...
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
|