Hi Robert,

Worksheet formulas are generally very fast, certainly compared to code doing the same thing.
For lots of info about Excel performance, visit Charles Williams' site:

www.decisionmodels.com

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"robs3131" wrote in message ...
| Thanks Tom and Steve. I was planning on turning off the screen update with
| my final code but I know that even with this it wouldn't speed it up.
|
| Tom - I will go ahead and use formulas. It was always my thought that it is
| faster to use code, even with intensive loops, than it is to use things such
| as vlookups or pivot tables or other formulas in code as it sems that Excel
| slows down significantly intermittingly when running such code. IE - it may
| run ok today, but tomorrow it will get hung up and show something such as
| "Calculating" or "Sort" or "Cells" at the bottom of Excel with bars showing
| where Excel is at in that process. Do either of you ever run into this
| intermittent issue? Just curious. Thanks.
| --
| Robert
|
|
| "Tom Ogilvy" wrote:
|
| 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