|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Range.Find gets slow
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 |
Range.Find gets slow
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 |
Range.Find gets slow
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 |
Range.Find gets slow
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 |
Range.Find gets slow
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 |
Range.Find gets slow
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 |
Range.Find gets slow
"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 |
Range.Find gets slow
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 |
Range.Find gets slow
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 |
Range.Find gets slow
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 |
Range.Find gets slow
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 |
Range.Find gets slow
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 |
Range.Find gets slow
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 |
| All times are GMT +1. The time now is 12:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com