I changed to code in the FindPrimary function as seen below. This
essentially resolves my current issues however the find has slightly more
flexibiltly such as doing reverse lookup and the ability to search using case
sensitivity. (Which I do use).

'Set c = rPrimary.Find(pValue, LookIn:=xlValues, lookat:=xlWhole)
nPos = Application.WorksheetFunction.Match(pValue, rPrimary, 0)
If Not nPos = 0 Then
FindPrimary = nPos + (rPrimary.Row - 1)
Print #1, pValue & " found.";
Else
Print #1, pValue & " not found.";


Thanks for you help! It was definately useful.


"Charles Williams" wrote:

Use MATCH, its faster than Find (and if you can sort the 11000 records it
will be several orders of magnitude faster) and works on hidden rows.
Also Redim Preserve is slow - you should do it in chunks of 100 with a final
redim preserve at the end if neccessary.

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"Guy Normandeau" wrote in message
...
I'm having two problems with the .find function.

When I run the code listed below the program runs extremely quickly when
the
records are found in the FindPrimary function. (You can see the timing and
results of a record found below the code.) When a record is not found in
the
range, it takes 3 seconds to return the results. This is not that bad if
you
have a few searches but I'm searching 11000+ records of which 60%+ will
not
always be found. Do the math and you'll see that it will seemlingly run
forever.

My question is why does it take so long to return a value when the record
is
not found and how can I fix this?

The other thing that i noticed is that while this program is running
(slowly), if I press the ESC key once (Only once), the program speeds up
dramatically and displays the c.row that is being processed, however the
find
returns nothing and does not generate any type of errors. Another thing
that
is strange is that the MSGBOX statement is not processed.

What does the ESC key do to the processing of the find functions?


...

For Each c In Range("tShipToCustomers")
Application.StatusBar = "Processing Row " & c.Row
If c.Offset(0, 4) = "X" Then
nRow = FindPrimary(c.Value)
If nRow = 0 Then
nShipToCnt = nShipToCnt + 1
If nShipToCnt = 1 Then
ReDim aShipTo(nShipToCnt)
Else
ReDim Preserve aShipTo(nShipToCnt)
End If
End If
End If
Next

Application.EnableEvents = True
Application.StatusBar = False

MsgBox nPrimaryCnt

Close

End Sub

Function FindPrimary(pValue As String) As Long

Dim c

FindPrimary = 0
Print #1, Time();
Set c = rPrimary.Find(pValue, LookIn:=xlValues)
If Not c Is Nothing Then
FindPrimary = c.Row
Print #1, c.Value & " found.";
Else
Print #1, pValue & " not found.";
End If
Print #1, Time()

End Function


Giving the follwing sample results:

10:23:30 AM 10000700 found.10:23:30 AM
10:23:30 AM 10026275 found.10:23:30 AM
10:23:30 AM 10026441 not found.10:23:33 AM
10:23:33 AM 10026442 not found.10:23:36 AM
10:23:36 AM 10026443 not found.10:23:39 AM
10:23:39 AM 10014346 found.10:23:39 AM
10:23:39 AM 10009650 found.10:23:39 AM
10:23:39 AM 10000452 found.10:23:39 AM
10:23:39 AM 10027244 not found.10:23:42 AM
10:23:42 AM 10026382 not found.10:23:45 AM
10:23:45 AM 10025933 found.10:23:45 AM
10:23:45 AM 10023339 found.10:23:45 AM
10:23:45 AM 10026422 found.10:23:45 AM


Results after press the ESC key once.
10:24:20 AM 10003727 not found.10:24:20 AM
10:24:20 AM 10023276 not found.10:24:20 AM
10:24:20 AM 10025921 not found.10:24:20 AM
10:24:20 AM 10001191 not found.10:24:20 AM
10:24:20 AM 10007567 not found.10:24:20 AM
10:24:20 AM 10001040 not found.10:24:20 AM
10:24:20 AM 10022305 not found.10:24:20 AM
10:24:20 AM 10001037 not found.10:24:20 AM
10:24:20 AM 10006902 not found.10:24:20 AM
10:24:20 AM 10000274 not found.10:24:20 AM
10:24:20 AM 10000255 not found.10:24:20 AM