Search column items in multiple sheets

Hello everyone
I have some items in column A in sheet1 ..and I intend to search for each item in the other sheets
The search will be through the used range of the sheet and return the value in the first column of the searched sheets
I have the following code that works well for small amounts of data but in real file it is very very slow so I am searching for a faster way

Please Login or Register  to view this content.

* The issue is posted here too
http://www.eileenslounge.com/viewtopic.php?f=30&t=29498

I have changed the UDF to that form

Please Login or Register  to view this content.

But I don't know which is faster :using loops as in the first post or using Index and match as in the second post ..
Can you guide me please?

Thanks a lot Mr. Jindon
So you mean looping through columns using Index and Match is faster than looping through the arrays using Index and Match ?
Can you help me improving the code?

Could this be a little faster ?

Code on ThisWorkbook Module :

Please Login or Register  to view this content.

Code on Standard Module :

Please Login or Register  to view this content.

That's great and fascinating Mr. Karedog
Thank you very very much

I am trying to apply it on the original file now .. Some cells has NA error so I encountered an error at this line

Please Login or Register  to view this content.

How to avoid error cells ? Would I add a line If Not Iserror(a(i, j)) before that line or that may slow down the process?

You are welcome.
I don't think so, it should be nothing (IsError is a builtin VBA function, it should be fast), and beside, the RefreshZ is only called once when refreshing the collection, so it is not depending on how many items on Sheet1!A:A

Please Login or Register  to view this content.

Really amazing. I didn't believe it can be done in that speed
You are amazing my friend

* One last question as for the UDF posted in the first post and the UDF posted in the second post : which is faster ...? or is there another udf that searches 2d array faster than these UDFs

* The main problem solved but I like to have a solution for that part too

Why don't you try to measure it by yourself ?
Just my guess, if number of rows of the array is little, the For..Loop will win, but if many, probably the Application.Match wins (since there is overhead calling a worksheet function).

BTW, if it is possible to use an array formula, then my code can be written as an UDF too (you need to delete all my previous code first) :

Please Login or Register  to view this content.

Usage : Array formula on Sheet1!B2:B5 :

Formula:

Please Login or Register  to view this content.

or even just :

Formula:

Please Login or Register  to view this content.

but you must manually recalc the sheet, or insert the Application.Volatile to the UDF

Thanks a lot my friend
I didn't mean the main issue .. It was solved by you completely and it was amazing solution .. I didn't mean to have a solution by converting your code to UDF

* I am talking about searching in 2D array ..
Here's the code

Please Login or Register  to view this content.

Is that UDF for searching the UDF is faster or not (I would not test .. because I need to know from you the better case) ..
Can this udf that would search 2D arrays ..can we use collections if faster?

Please Login or Register  to view this content.

Well well ...
Here I have set up the test code for both of your UDF (using For..Loop and Application.Match)
Just run the code, and examine the result (I still don't understand why everybody is so afraid of looping)
You can also change number of rows by changing the line with red color, for example from 100 to 10000

As for collection, no, collection won't make the UDF faster if it is used in your SearchIn2DArray() UDF, since this UDF is called as any time as number of items on Sheet1!A:A. In fact, it will be slower, since array operation (a simple variable) is much faster than collection (an object).

Perfect my friend
That's exactly what I was seeking for .. I was seeking for such a demonstration .. Now I would support loops I won't be afraid of loops inside arrays anymore
Thanks a lot for great and incredible help
Best and kind regards

You are welcome, thanks for marking the thread as solved and rep.points.

Regards