[發問] for next 迴圈速度慢，如何改速度能加快

chiang0320

test.zip (93.13 KB)

下載次數: 11

2016-12-2 16:35

   

下載 (7.99 KB)

2016-12-2 16:36

M欄為變數，有可能數量會到十萬或二十萬...
利用迴圈速度很慢，如何可以加快速度

Public Sub WWW()

Start = Timer

Range("K3") = "=COUNTIF(M:M,""*RBW*"")"
    a = Range("K3")
        For i = 4 To a + 3

            Cells(i, "N") = "=IFERROR(AVERAGEIF($D:$H,M" & i & ",E:H),""0"")"
            Cells(i, "O") = "=IFERROR(COUNTIF(D:D,M" & i & "),""0"")"

Next i

Range("M:O").Value = Range("M:O").Value
      
Finish = Timer
TotalTime = Finish - Start
MsgBox "已完成!  總共：" & TotalTime & "秒 !"

End Sub

准提部林

1. Sub WWW()
   
2. Dim Arr, Brr, xD, i&, j%, N&, SS, TM
   
3. TM = Timer
   
4. Set xD = CreateObject("Scripting.Dictionary")
   
5. Arr = Range([M4], Cells(Rows.Count, "M").End(xlUp))
   
6. ReDim Brr(1 To UBound(Arr), 1 To 2)
   
7. For i = 1 To UBound(Arr)
   
8.     If Arr(i, 1) <> "" Then xD(Arr(i, 1)) = i
   
9. Next i
   
10. 
    
11. Arr = Range([D4], Cells(Rows.Count, "D").End(xlUp)(1, 5))
    
12. For i = 1 To UBound(Arr)
    
13.     N = xD(Arr(i, 1)): If N = 0 Then GoTo 101
    
14.     SS = Split(Mid(Brr(N, 1), 2) & "/", "/")
    
15.     For j = 2 To 5
    
16.         If IsNumeric(Arr(i, j)) Then
    
17.            SS(0) = Val(SS(0)) + Arr(i, j)
    
18.            SS(1) = Val(SS(1)) + 1
    
19.         End If
    
20.     Next j
    
21.     Brr(N, 1) = "=" & SS(0) & "/" & SS(1) '=總合計/個數
    
22.     Brr(N, 2) = Brr(N, 2) + 1
    
23. 101: Next i
    
24. 
    
25. [N4].Resize(UBound(Brr), 2) = Brr
    
26. MsgBox "已完成!  總共：" & Timer - TM & "秒 !"
    
27. End Sub

複製代碼

Xl0000001.rar (91.71 KB)

下載次數: 18

2016-12-2 22:02

chiang0320

回復 2# 准提部林


Dear 准提部林 :

謝謝幫忙，跑完才花費不到一秒鐘

對於初學持者但太深奧了!很想學起來但不知如何問起 =_=