I created a function that runs too slow! Can you help me?

Hi everyone!

I just created a FUNCTION to find the column number of a cell in other BOOK depending of two variables. For 100 cells it takes 3 minutes to calculate!

THIS IS MY CODE:

Please Login or Register  to view this content.

Deleted while OP complied to Forum Rules.

I would suggest that you come up with a better searching algorithm. As programmed, it looks like a very inefficient "start in the upper left corner and go across and down until it finds the cell that matches the two criteria". I think a better search algorithm could improve the performance of this.

First thing I notice, it appears that you are only going to return a value from the row which has takt in the leftmost column. As presently programmed, your search algorithm wastes a lot of time/effort searching across rows that do not have takt in the first column of that row. I would structure the algorithm so that it first searches for takt in the first column. Once the algorithm knows which row takt is in, then look for the column in that row that contains wagon.

Does that make sense? It appears that you already know how to code a basic linear search algorithm, so can I assume you can rearrange your loops to change the search order?

I'd be curious what you tried. You seem to know For...Next loops and Block If's and such, so it seems like it should have been fairly straightforward. I would have expected like this code snippet:

Please Login or Register  to view this content.

I would even lose the column search.

Please Login or Register  to view this content.

I am sorry guys but why would anyone search through a whole lot of cells looking for specific text?

Surely you would use the Find function?

What am I missing?

Please Login or Register  to view this content.

mehmetcik: I did not suggest the .Find method because I was not sure how well it would work for this kind of 2D lookup, especially if there are multiple instances of wagon in the table and the goal of the lookup is to find which instance of wagon is in the takt row. The OP really hasn't given enough information for me to suggest how to use .Find in this situation (though that could easily be because I don't use .Find enough to see how to use it here).

For that matter, why use VBA at all. I expect there is a way to combine INDEX() and MATCH() functions to perform this lookup without VBA. That could also help, just because VBA requires a certain overhead that can contribute to poor performance. I guess we are just working from the OP's starting place.