Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
optimize cycle's speed - For, Next or DO, UNTIL; IF-THEN, ENDIF or CASE, ENDCASE
Hi,
I have cycle which is checking duplicates names in column 12. For j = 1 To 65000 dupl = Sheets("Sheet1").Cells(j, 12).Value If dupl = strText Then c = c + 1 numbere = numbere + 1 Else End If Next j The cycle become slower when j is growing. I want to optimize cycles for speed. Should I change For, Next cycle to DO, UNTIL (or any other)? Should I change IF-THEN, ELSEIF, ELSE, ENDIF to CASE, ENDCASE (or any other)? Is this effecting speed? Thank you -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
optimize cycle's speed - For, Next or DO, UNTIL; IF-THEN, ENDIF or CASE, ENDCASE
You need to leverage the fact that you are working with VB*A*; focusing
on VB syntax will, if you are lucky, give you a tiny % in improvement. Focusing on the XL object model has the potential to be significantly more rewarding. Look up the XL VBA help files for the Find method. Let XL do the work for you! Or use the COUNTIF formula, as in MsgBox Application.WorksheetFunction.CountIf( _ Range("a1:a65536"), Range("a1").Value) It takes a fraction of a second to get an answer when all 65,536 rows contain data. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , Oleks says... Hi, I have cycle which is checking duplicates names in column 12. For j = 1 To 65000 dupl = Sheets("Sheet1").Cells(j, 12).Value If dupl = strText Then c = c + 1 numbere = numbere + 1 Else End If Next j The cycle become slower when j is growing. I want to optimize cycles for speed. Should I change For, Next cycle to DO, UNTIL (or any other)? Should I change IF-THEN, ELSEIF, ELSE, ENDIF to CASE, ENDCASE (or any other)? Is this effecting speed? Thank you. --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP! optimize calculating values | Excel Discussion (Misc queries) | |||
How to end my macro in If-else-Endif | New Users to Excel | |||
Optimize SumProduct | Excel Discussion (Misc queries) | |||
Optimize SumProduct | Excel Worksheet Functions | |||
How can I optimize this code? | Excel Programming |