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

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

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


"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

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

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

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

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

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

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

"vbapro" wrote in message
...
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" 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

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

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€„¢ 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

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

vbapro wrote:

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€„¢ 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


--

Dave Peterson

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

"Peter T" <peter_t@discussions wrote in message
<snip

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

I see Dave had already posted his findings

Peter T

Findings = lack of findings!!!

Peter T wrote:

"Peter T" <peter_t@discussions wrote in message
<snip

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

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" wrote in message
...
Findings = lack of findings!!!

Peter T wrote:

"Peter T" <peter_t@discussions wrote in message
<snip

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

I bet the OP will disagree! <vbg

Peter T wrote:

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" wrote in message
...
Findings = lack of findings!!!

Peter T wrote:

"Peter T" <peter_t@discussions wrote in message
<snip

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

--

Dave Peterson

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" wrote:

I bet the OP will disagree! <vbg

Peter T wrote:

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" wrote in message
...
Findings = lack of findings!!!

Peter T wrote:

"Peter T" <peter_t@discussions wrote in message
<snip

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

--

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.

vbapro wrote:

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" wrote:

I bet the OP will disagree! <vbg

Peter T wrote:

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" wrote in message
...
Findings = lack of findings!!!

Peter T wrote:

"Peter T" <peter_t@discussions wrote in message
<snip

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

--

Dave Peterson


--

Dave Peterson

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

"Dave Peterson" wrote in message
...
But for me, I couldn't tell the difference between searching through
formulas or
values.

I still don't have a guess, though.

vbapro wrote:

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" wrote:

I bet the OP will disagree! <vbg

Peter T wrote:

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" wrote in message
...
Findings = lack of findings!!!

Peter T wrote:

"Peter T" <peter_t@discussions wrote in message
<snip

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

--

Dave Peterson


--

Dave Peterson