I’m creating an extremely complicated workbook for a large client using Excel VBA (I tried to convince them to go database but hey….they’re paying the rate so there it is). I need to retrieve a single row or cell of data from a sheet using multiple selection criteria. VLOOKUP can’t do it and INDEX/MATCH is turning out to be quite the nightmare. I’ve had to resort to filtering the sheet by all the criteria and then copying the visible results to a temp worksheet (filter doesn’t remove the header row throwing off counts and data) and then sifting through those results to find that one tiny piece of data I need. Doing this literally tens-of-thousands of times to make a complete run has my project taking 3-4 hours to perform all it’s calculations…not good.
I have entered test queries using the SQL Addin tool and retrieved the exact row I’m looking for in less than a tenth of a second (pretty drastic improvement, eh?). Ultimately what I need to is create a few queries with variables for the WHERE criteria and then execute them as needed from within my VBA code.
Can someone tell me how to do that or point me in the direction of an article or document that will explain this to me?
Glad you figured it out Mark.
I placed some example SQL queries here:
Also look at this post where I show how o analyze a CSV line by line. You can similarly for an Excel spreadsheet by changing the data source: