The book Power-Up Using Excel VBA Sorts and Searches: Advanced Techniques for Lightning Fast Sorts and Searches by Rich Locus presents VBA code, that can be used for Searches and Sorts.
The idea behind the book is to present the commands VLookup, Match and Range.Find in VBA. As you probably know, Vlookup and Match could be forced to return only the exact value (with the parameter FALSE on the formula) or a similar value (with the parameter TRUE). The idea is, that if you are forcing the return of the exact value, with a FALSE, the formulas become really slow. Thus, it takes a while to calculate the whole spreadsheet. The usage of TRUE is obviosly faster, but if an exact value is not found a similar is returned. So we have the following situation, represented in a simple matrix here:
The key for the solution? A way to have the exact result in a quick speed – well, the book says it is the following:
- Sort the range with VBA;
- Use the TRUE case;
- Edit VBA your code in a way to catch, if the solution is not the exact one and act appropriately;
Pretty much that is all. The book comes up with 100% of the code, which it uses is a noticable plus. The code is explained and the task is solved! If you really have problems with sorting and searching – the book will help you! It does not do more than that – it resolves a for searching and sorting in VBA. Anyway, it does not claim that it does more.
Enjoy it! 🙂