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

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/