I've got your file but don't notice the time varies significantly between
successive use of Find. LookIn xlFormulas is only marginally faster than
xlValues, and only then if using a more accurate timer. Of course the Find
may take very much longer if the string is found in a large number of cells,
eg "LCD" is in +20k cells and does take a long time (but similar on
subsequent searches in values or formulas).
In passing -
you don't need the Exit Do as
' If Not Rng Is Nothing Then
FoundCount = FoundCount + 1
' Else
' Exit Do
' End If
Loop While Not Rng Is Nothing And Rng.Address < firstAddress
Normally it's advisable to complete other optional arguments that may have
been left in a different state by user, eg
LookAt:=x;Part, _
SearchOrder:=xlyRows, _
SearchDirection:=xlNext, _
MatchCase:=False
For about 18x more accuracy than your 'nearest to one second' timer try
Dim t As Single
t = Timer
'code
t = Timer - t ' seconds
Keep your fingers crossed that Dave might see what's slowing down your Find
!
Regards,
Peter T
..
"vbapro" wrote in message
...
Thank you, Peter,
Thank you, Dave!
Q:Do you have lots of conditional formatting on that sheet?
A:No
Q:Do you have Google Desktop running?
A:On one PC - yes, on another - no. The effect is the same.
I have remade the worksheet so that it contains no important info and on
the
other hand allows observing the difference in the search speed.
You can download the example price_test.zip from
http://www.4shared.com/dir/5259317/3...e/sharing.html
"Dave Peterson" wrote:
Just some silly questions...
Do you have lots of conditional formatting on that sheet?
Do you have Google Desktop running?
vbapro wrote:
Unfortunately I have not succeeded to recreate and to model the
situation,
the effect appears on a particular worksheet only. But I have noticed
that
when I change ?~Look In?T option from Values to Formulas in the Find
dialog
then the search goes fast again.
I thing I will use search in Formulas,
Thanks!
"Peter T" wrote:
OK I tried to recreate what you describe but all seems to work at
roughly
same speed in multiple calls to find. Can you describe a simplified
scenario that's easy for others to recreate that will demonstrate
the
problem.
Regards,
Peter T
"vbapro" wrote in message
...
Thank you, Peter!
I must have unclearly explained. The problem is in the following.
There
are
about 20000 cells searched. First time when I use the standard
Find dialog
box (Ctrl+F in Excel Worksheet view), the results are found in
about a
second. After I have run the Find method of a Range object
programmatically,
the standard search looks slowly over the cells, what is seen in
the Name
Box.
"Peter T" wrote:
Nothing obvious in what you have shown that would make it slow,
I half
expected something else in your loop that would have done.
I just gave your code a pretty severe test in my very old setup,
Find
*a-word* in a long string in 200 cells scattered in 500k cells.
It
populated
the list with 200 row numbers very quickly.
What is .Find and .FindNext qualified with, how many 'found'
cells
(include
a counter in the loop), any difference if you comment the code
that
populates the list.
Regards,
Peter T
"vbapro" wrote in message
...
Thank you for the interest!
The code fragment is used in a UserForm. It fills a listbox
Set Rng = .Find("*" & What & "*", LookIn:=xlValues)
If Not Rng Is Nothing Then
firstAddress = Rng.Address
Do
If Not Rng Is Nothing Then
Me.ListBox1.AddItem Rng.Row
Else
Exit Do
End If
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <
firstAddress
End If
"Peter T" wrote:
What's in this bit
If Not Rng Is Nothing Then
Else
Regards,
Peter T
"vbapro" wrote in message
...
After I use Range.Find method programmatically, the
standard
search on
sheets
dramatically slows down. What could be the reason and how
can this
side
effect be taken out? Thanks!
After I use Range.Find method programmatically, star=ndard
it
searches
The relevant code is
Set Rng = .Find("*" & What & "*", LookIn:=xlValues)
If Not Rng Is Nothing Then
firstAddress = Rng.Address
Do
If Not Rng Is Nothing Then
...
Else
Exit Do
End If
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And (Rng.Address <
firstAddress)
End If
--
Dave Peterson