Loop works correctly but takes extremely long to execute

[robs3131]

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

 

[steve_doc]

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

 

[Tom Ogilvy]

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.

 

[robs3131]

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.

 

[Niek Otten]

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


| 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

 

[Tom Ogilvy]

I don't think it will be an issue with this workbook, but
the compromise is to have your code put in the formulas, then replace the
formulas with the values they return

With Range("B2:B281")
.Formula = "=countif('Transaction Summary'!$A$2:$A$3000,A2)"
.Formula = .Value
end With

as an example.

 

[robs3131]

Thanks Tom and Nick!! This helps.

--
Robert

I don't think it will be an issue with this workbook, but
the compromise is to have your code put in the formulas, then replace the
formulas with the values they return

With Range("B2:B281")
.Formula = "=countif('Transaction Summary'!$A$2:$A$3000,A2)"
.Formula = .Value
end With

as an example.