Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing Excel Code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing Excel Code
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 " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Optimizing Excel Reading | Excel Programming | |||
optimizing excel work sheet | Excel Programming | |||
Optimizing Code | Excel Programming | |||
optimizing code? (hide) | Excel Programming | |||
Optimizing in VB | Excel Programming |