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 |
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 |