Range.Find gets slow

[vbapro]

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

 

[Peter T]

What's in this bit

If Not Rng Is Nothing Then

Else

Regards,
Peter T

 

[Joel]

Try this code

Set Rng = .Find("*" & What & "*", LookIn:=xlValues)

If Not Rng Is Nothing Then
firstAddress = Rng.Address
For each cell in Rng
'enter your code here
next cell
End If

 

[vbapro]

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]

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]

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]

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]

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

It does sound like something particular to your worksheet. I wonder what
though. Maybe you can eliminate stuff until Find works normally.

But I have noticed that
when I change 'Look In' option from Values to Formulas

Just curiosity, any difference if you pastespecial values back to your
formula cells, ie no formulas on the sheet, then does 'Look in Values' work
consistently.

Regards,
Peter T

 

[Dave Peterson]

Just some silly questions...

Do you have lots of conditional formatting on that sheet?

Do you have Google Desktop running?

 

[vbapro]

Thank you, Peter,
Thank you, Dave!

Qo you have lots of conditional formatting on that sheet?
A:No

Qo 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/333c7e6e/sharing.html

 

[Dave Peterson]

I don't (usually) download files, but I did this time.

I couldn't duplicate any problem in xl2003.

I ran the code a few times (both formulas and values) and then did a few times
manually and didn't notice any speed problem.

Another silly guess...

Try changing the font to Arial. Maybe it's something to do with the display
speed?????

 

[Peter T]

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

 

[Peter T]

Keep your fingers crossed that Dave might see what's slowing down
your Find !

I see Dave had already posted his findings

Peter T

 

[Dave Peterson]

Findings = lack of findings!!!

I see Dave had already posted his findings

Peter T

 

[Peter T]

Oh I wouldn't say that - you found there was nothing to find, sounds like a
finding to me <g>

Regards,
Peter T

 

[Dave Peterson]

I bet the OP will disagree! <vbg>

Oh I wouldn't say that - you found there was nothing to find, sounds like a
finding to me <g>

Regards,
Peter T

 

[vbapro]

I have tried to reproduce the problem in the original file, and now it seems
to me that I had been confused by the following.

When you open Excel the LookIn option in the Find dialog box is set to
xlFormulas. In my code I used LookIn:=xlValues, and according to the help,
the options of Range.Find method and in the Find dialog box correspond to
each other and are saved every time they changed during one Excel session.
So, after my code has been run xlValues was used in the Find dialog box.
Apparently, then search with xlValues is slower than with xlFormulas. This
had confused me.


Thank you!

 

[Dave Peterson]

But for me, I couldn't tell the difference between searching through formulas or
values.

I still don't have a guess, though.

I have tried to reproduce the problem in the original file, and now it seems
to me that I had been confused by the following.

When you open Excel the LookIn option in the Find dialog box is set to
xlFormulas. In my code I used LookIn:=xlValues, and according to the help,
the options of Range.Find method and in the Find dialog box correspond to
each other and are saved every time they changed during one Excel session.
So, after my code has been run xlValues was used in the Find dialog box.
Apparently, then search with xlValues is slower than with xlFormulas. This
had confused me.

Thank you!

 

[Peter T]

I didn't 'find' any noticeable difference but an accurate timer showed it
was perhaps marginally faster to search in formulas than values.

Regards,
Peter T

But for me, I couldn't tell the difference between searching through formulas or
values.

I still don't have a guess, though.