Hi all,

I have a major issue right now with the time it is taking for two queries to
execute. This is actually realated to an issue I posted a couple of days ago
("Issue with refreshing pivot tables in my code" posted on 6/4/07).
Regarding the more recent issue, the code below takes over 10 minutes (at
least) to execute -- I always just kill the query after a period of time. It
doesn't make sense to me as to why it would take this long.

This is very concerning because the end product will have significantly more
data than what I am using for testing (there are 5997 records on the "Member
ID Report Master" sheet and only 3 records on the "Payment Sales Master"
sheet currently).

Does anyone have any idea as to why it would take Excel so long to execute
this query??? Might it have something to do with the fact that I had pivot
tables originally in this file that I was refreshing via the code? FYI - I
decided to get rid of the pivot tables (they are now deleted from the file)
and essentially replicate the functionality of the pivot table through the
code with the thought that the time to execute the query would decrease
signficantly by not using pivot tables. I've found that this has not been
the case... for more info on my issue with the pivot tables, please see my
post titled "Issue with refreshing pivot tables in my code" posted on 6/4/07
-- FYI -- I have not received a response to that post).


Sub opentransids()

Dim uniqueidsopen As Range
Dim F 'individual record within "uniqueidsopen" range
Dim payclosed As Range
Dim G 'individual record within "payclosed" range
Dim x 'variable to determine how many rows down to input uniqueidsopen
Dim c 'variable to determine whether or not a uniqueidsopen is also within
opentrans

With Sheets("Unique Member IDs")
If Len(.Range("A3")) < 0 Then
Set uniqueidsopen = .Range("A2", .Range("A2").End(xlDown))
Else
Set uniqueidsopen = .Range("A2")
End If
End With

With Sheets("Payment Sales Master")
If Len(.Range("H3")) < 0 Then
Set payclosed = .Range("H2", .Range("H2").End(xlDown))
Else
Set payclosed = .Range("H2")
End If
End With

x = 1
For Each F In uniqueidsopen
c = 0
For Each G In payclosed
If F = G Then
c = c + 1
Else
End If
Next

If c = 0 Then
With Sheets("Open Transactions")
.Range("D1").Offset(x, 0).value = F
x = x + 1
End With
Else
End If
Next

End Sub



Thanks!
--
Robert