• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to make For Each Loop faster

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

I am using below mentioned loop. Last row is 75,000 +.
This loop is taking too much time to complete.
Could you please suggest how can I make this loop run faster.

Code: 
For Each TRng In TempRng
        Application.StatusBar = TRng.Row
        MySht.Cells(TRng.Row, 1).FormulaR1C1 = "=IF(AND(COUNTIFS(C[1],RC[1],C[9],""QQ*"")>0,COUNTIFS(C[1],RC[1],C[9],""WW*"")>0),""A"",""B"")"
        MySht.Cells(TRng.Row, 1).Value = MySht.Cells(TRng.Row, 1).Value
    Next TRng
    Application.StatusBar = False

I tried edited version of below code.
But this is also not making any difference.

Code: 
Sub test()

Dim varray As Variant
Dim i As Long

varray = Range("A2:A10").Value

'must step back or it'll be infinite loop
For i = UBound(varray, 1) To LBound(varray, 1) Step -1
    'do your logic and evaluation here
    If varray(i, 1) = "foo" Then
      'not how to offset the i variable
      Range("A" & i + 2).EntireRow.Insert
    End If
Next

End Sub
 
before
Code: 
    With Application
        cm = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
[code]

within loop
[code]
'a)    hide next line
'    Application.StatusBar = TRng.Row
'    if need some statues then show eg every 500 row's status ...
'b)    modify next two lines, that You'll get straight result
''    MySht.Cells(TRng.Row, 1).FormulaR1C1 ''= "=IF(AND(COUNTIFS(C[1],RC[1],C[9],""QQ*"")>0,COUNTIFS(C[1],RC[1],C[9],""WW*"")>0),""A"",""B"")"
''    MySht.Cells(TRng.Row, 1).Value = MySht.Cells(TRng.Row, 1).Value

after
Code: 
    With Application
        .ScreenUpdating = True
        .Calculation = CM
    End With
 
Hi @Debaser thanks a lot for the help, I had tried using formula only.
But machine gets hang. Operation does not get complete. Hence trying loop.

Have a nice day ahead. :)
 
ThrottleWorks
Many things would take time ... if You do something for ~75k rows.

eg Did You change that it will make straight results?
Is there something else ... which You didn't show to us?

You asked 'how to make that faster?'
How much those made that faster, did You compare?
 
Looping through cell by cell will never be faster. Your formula could be more efficient since it always does both COUNTIFS even if the first one fails, so perhaps use:

Code: 
"=IF(COUNTIFS(C[1],RC[1],C[9],""QQ*"")>0,IF(COUNTIFS(C[1],RC[1],C[9],""WW*"")>0,""A"",""B""),""B"")"

If that's still too slow, I think you would need to scrap this approach entirely and use something else, like a combination of array and dictionary.
 
You must log in or register to reply here.
Back
Top