I left one c = 0 out. It should be:

x = 1

With Sheets("Unique Member IDs")

If Len(.Range("A3")) < 0 Then

uniqueidsopen = .Range("A2", .Range("A2").End(xlDown))

With Sheets("Payment Sales Master")

If Len(.Range("H3")) < 0 Then

payclosed = .Range("H2", .Range("H2").End(xlDown))

For F = 1 To UBound(uniqueidsopen)
c = 0

etc.


RBS


"RB Smissaert" wrote in message
...
Not sure I got the logic right, but try this:

Sub opentransids()

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

Application.ScreenUpdating = False

x = 1

With Sheets("Unique Member IDs")

If Len(.Range("A3")) < 0 Then

uniqueidsopen = .Range("A2", .Range("A2").End(xlDown))

With Sheets("Payment Sales Master")

If Len(.Range("H3")) < 0 Then

payclosed = .Range("H2", .Range("H2").End(xlDown))

For F = 1 To UBound(uniqueidsopen)
For G = 1 To UBound(payclosed)
If uniqueidsopen(F, 1) = payclosed(G, 1) Then
c = c + 1
Exit For
End If
Next

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

Else

payclosed = .Range("H2")

For F = 1 To UBound(uniqueidsopen)
If uniqueidsopen(F, 1) = payclosed Then
c = c + 1
Exit For
End If

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

End If
End With
Else

uniqueidsopen = .Range("A2")

With Sheets("Payment Sales Master")

If Len(.Range("H3")) < 0 Then

payclosed = .Range("H2", .Range("H2").End(xlDown))
For G = 1 To UBound(payclosed)
If uniqueidsopen = payclosed(G, 1) Then
c = c + 1
Exit For
End If
Next

If c = 0 Then
With Sheets("Open Transactions")
.Range("D1").Offset(x, 0).Value = uniqueidsopen
x = x + 1
End With
End If

Else
payclosed = .Range("H2")
If uniqueidsopen < payclosed Then
.Range("D1").Offset(x, 0).Value = uniqueidsopen
End If
End If
End With
End If
End With

Application.ScreenUpdating = True

End Sub

There are 3 changes:
Work on arrays rather than ranges.
Early exit from loops.
Application.ScreenUpdating = False

If it doesn't work then you will have to fiddle about, but hopefully you
will get the idea.
Depending on the data this could speed it up a lot.


RBS


"robs3131" wrote in message
...
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