slow processing with .find

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

You could try adding
Lookat:=xlWhole
to the Find.
Might speed things up if Excel doesn't have to check for substrings...
--
Tim Williams
Palo Alto, CA


"Guy Normandeau" <GuyNormandeau@discussions.microsoft.com> wrote in message
news:5741EB8E-2B78-4E19-BD44-8FC78AB553BF@microsoft.com...
> 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
>
>

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" <GuyNormandeau@discussions.microsoft.com> wrote in message
news:5741EB8E-2B78-4E19-BD44-8FC78AB553BF@microsoft.com...
> 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
>
>

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
>
>

There were no changes when using the Lookat.

"Tim Williams" wrote:

> You could try adding
> Lookat:=xlWhole
> to the Find.
> Might speed things up if Excel doesn't have to check for substrings...
> --
> Tim Williams
> Palo Alto, CA
>
>
> "Guy Normandeau" <GuyNormandeau@discussions.microsoft.com> wrote in message
> news:5741EB8E-2B78-4E19-BD44-8FC78AB553BF@microsoft.com...
> > 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
> >
> >
>
>
>

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" <GuyNormandeau@discussions.microsoft.com> wrote in message
> news:5741EB8E-2B78-4E19-BD44-8FC78AB553BF@microsoft.com...
> > 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
> >
> >
>
>
>

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
> >
> >