| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range.Find gets slow
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range.Find gets slow
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range.Find gets slow
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range.Find gets slow
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range.Find gets slow
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range.Find gets slow
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range.Find gets slow
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find & Replace Very Slow in VB But Not Manually | Excel Programming | |||
| ADO - recordset find performance slow | Excel Programming | |||
| slow processing with .find | Excel Programming | |||
| Slow 'FIND' when entering a value in Combobox | Excel Programming | |||
| Find function very slow, when XLS window is not active | Excel Programming | |||
Hybrid Mode
