1 thaing that will help speed this up
Turn off ScreenUpdating at the start of the procedure
And turn it back on again at the end.

Syntax - Application.ScreeUpdating = False 'Turn Off
Application.ScreeUpdating = True 'Turn On

Might only be anominal time saving - but it is a time saving

"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