Optimization of the Find() Function

[pierre08]

Hi guys,

I use a lot the function “Find” in Excel but the problem is that it takes a lot of time, so I’m searching for another function or code that can be faster than that, the Worksheets that I use in Excel contain thousands of sheets so it takes hours to execute the Macro.

Please Helpppppp

[rwgrietveld]

Find is pretty fast itself.

need to look at your code to find an optimization.

Have you tried things like:

[pierre08]

I use all this functions in all my macros. I have a lot of diffrent code where i use "Find", so i need a universal function that can replace Find to reduce the time of execution, this is what i'm searching for, i tried to replace it with many loop but it was even slower.

[romperstomper]

There is no generic alternative. There might be things that are faster in particular situations, but generally speaking, as mentioned, Find is pretty fast. If you truly have thousands of sheets in your workbooks, then your problem is your workbook structure - anything that has to search through all of them is going to take time.

[pierre08]

I usualy use the function like this:

Cells.Find(what:="case 2", after:=Range("A2")).Select

I tried to replace it with several codes but it was even slower then find like for exemple a search in each colomne using a loop.

[romperstomper]

Select is almost never necessary and is slow and inefficient, so you should avoid it. Besides that you shouldn't use a Find and Select in the same operation since it will error if the data is not found.

[rwgrietveld]

And you search in all cells. Can you use

Please Login or Register  to view this content.

and seing this I wonder how you loop through all the sheets
I guess it will be something like

Please Login or Register  to view this content.

as I do not see a Ws1.cells.Find ...

[pierre08]

Well in fact i tried to search column by column, so if the number that i'm searching for is at the begining it's even faster that Find but if it's far il will take a lot of time

What do you think, is there another way???

[pierre08]

Hey guys,

Is it possible to use an autoFilter in the WorkSheets, can it be faster to Find a Value?
What do you think?

[pierre08]

Is it possible to use an autoFilter in the WorkSheets, can it be faster to Find a Value?

What do you think guys.??
Does anyone have an exemple for that?

[romperstomper]

It depends. If you are looking for one occurrence, find will be faster. (again, Find is not inherently slow)

[pierre08]

Hi guys,
if i used an array: worksheet= array, can the search be faster? and does anyone knows the code to do that?
Thank you

[romperstomper]

Doubtful - you would have to loop through every item in the array. It sounds to me like you need a database, or at least a better workbook structure. I can't think of any good reason why you would need to search every column for a piece of data - if you know what kind of data you are looking for, you should only be searching the columns that contain it. You would also still be better off not having so many sheets - it will make everything a lot easier.