I did declare the variable c as Range but that did nothing to resolve the
issue with the find.

"Jim Thomlinson" wrote:

Fully declare your variables... Dim c makes c a variant which is the slowest
of all variable types...
--
HTH...

Jim Thomlinson


"Guy Normandeau" wrote:

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