Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop works correctly but takes extremely long to execute
Hi all,
Below is a sub I created to sum up values from a given column similar to what a Pivot Table would do (from my limited experience, it seems that refreshing pivot tables using vba causes the code to hang up or take very long to execute). The issue is that it simply takes forever for the code to execute - literally I can see it summing up values in each cell as it executes - so slow that I can see each value in the cell as it changes. I have written similar code before that executes much much faster...I was thinking that maybe I just needed to use the vba code cleaner, but even after using that, the code executes at the same speed. I'm wondering if it does make sense to you that it would take extremely long for this code to execute (there are a total of 280 rows of data in range2 and 2071 records in range3). Sub bringit() 'Calculate and populate commissions by unique Member ID With Sheets("Open Transactions by Member ID") If Len(.Range("A3")) < 0 Then Set range2 = .Range("A2", .Range("A2").End(xlDown)) Else End If End With With Sheets("Transaction Summary") If Len(.Range("A3")) < 0 Then Set range3 = .Range("A2", .Range("A2").End(xlDown)) Else End If End With With Sheets("Open Transactions by Member ID") If Len(.Range("A2")) < 0 Then For Each B In range2 tottran = 0 opentran = 0 openeligtran = 0 For Each C In range3 If B = C Then tottran = tottran + C.Offset(0, 9) If C.Offset(0, 12).Value = "Open" Then opentran = opentran + C.Offset(0, 9) Else If C.Offset(0, 12).Value = "Payment Issued" Then openeligtran = openeligtran + C.Offset(0, 9) Else End If End If Else B.Offset(0, 1).Value = tottran B.Offset(0, 3).Value = opentran B.Offset(0, 4).Value = openeligtran B.Offset(0, 2).Value = tottran - opentran End If Next Next Else End If End With End Sub -- Robert |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting takes too long | Excel Worksheet Functions | |||
VLookup takes too long | Excel Worksheet Functions | |||
Clearing cells takes long, long time | Excel Discussion (Misc queries) | |||
Extremely long SQL Array | Excel Programming | |||
PasteSpecial takes too long | Excel Programming |