your making 280 x 2071 = 579880 loops.

You could easily do this formulas in 'Open Transactions by Member ID'
B2:
=countif('Transaction Summary'!$A$2:$A$3000,A2)
D2:
=Sumproduct(--(A2='Transaction Summary'!$A$2:$A$3000),--('Transaction
Summary'!$M$2:$M$3000="Open"),'Transaction Summary'!$J$2:$J$3000)
E2:
=Sumproduct(--(A2='Transaction Summary'!$A$2:$A$3000),--('Transaction
Summary'!$M$2:$M$3000="Payment Issued"),'Transaction Summary'!$J$2:$J$3000)
C2: =B2-D2
=

and so forth.

If you don't want to put the formulas in the worksheet directly, at least
use them in your code.

--
Regards,
Tom Ogilvy




"robs3131" wrote:

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