A better way to store/retrieve bulk data?

can you post some details? are you using lookup functions in the sheet, like vlookup() or Index(), or are the "dozens of lookups" happening in the code with .find() ??

can you at least post some of the code? also, yes there are ways to connect excel directly to other databases. there are built in 'canned' microsoft queries you can hard-wire to the worksheet and you can also use ADO to query a datassource in VBA.

you also have the option of using power-pivot for large sets of data if you are using excel 2010, that might be useful to you... but depends on what you're doing exactly.

PowerPivot is probably not the answer here (doesn't sound like pivot tables will help you).

However, your big time lag is having the code loop through every cell in the range. I would suggest altering the custom function to make use of the .find() method and the Union operation.

Basically, you can greatly improve things by building a range that only contains 'hits'. You can then do what you will with the values in those cells. By doing this, instead of looping 150000 times... you will only loop for the number of matches.

Would go something like this code below:

Please Login or Register  to view this content.

oh man, i forgot a really important step in the code -- i edited my previous post and added that in.

Set c = .FindNext(c)

You should be getting matches with that line, you may have to change, what:=lookfor to something like what:="*" & lookfor &"*" but try just adding in the line i forgot before bothering with the "what" parameter (I'm pretty sure that find is already looking for the text anywhere within the cell value).


Also, once you have the matches, you won't need to perform any logical checks on the range "matches" -- all the cells found will contain the text.

So, just go with:

Please Login or Register  to view this content.

Offset(0,0) is also not necessary (means no offset).

Are you stepping through the code? Is the code making it into the IF statement at all ?

Ok, take a look at the attached workbook to test this out. I am confident this can greatly speed up your code...

Since you can't upload an example, i put together the function and a simple test Sub to show it in action. Open the workbook, enable the macros and press the button to test the function.
You can step through the code to see it working. Press Alt+F8 to see the macro, select "test" and press "Step Into". Press F8 to take each step.

I have a message box pop up to show the results, and i also list them in the sheet in the A column. I think you had a different final step, but maybe this will be something you can edit to meet your needs.

Please Login or Register  to view this content.

Originally Posted by daedelous00

Does "hit" need to be defined somewhere?

Well, it's defined in the "dim" statements at the top of the function (Dim hit as Range). When you use a FOR EACH style loop in Excel, you are looping over a "collection".

I don't have to define "hit" any more than with the Dim statement. When i reference a Range variable in that FOR EACH loop, Excel determines what "hit" should be interpreted as based on the variable type and the Collection I've given it (the collection is the other range variable "matches").

Think of it like this: For Each member In collection --- that is the general syntax.

Take a look here for some more examples and descriptions. http://www.excelfunctions.net/VBA-Loops.html

ok. Added some variables you can edit and then i think this will be exactly what you are asking for.

You're going to have to tell the macro the following:

comments sheet name, report (or destination) sheet name, header to look for to find the 'values' column on the report sheet, header to look for to find the 'match' column on the comments sheet.


Run the example and I think you'll see what I'm talking about. Attached updated workbook.

Please Login or Register  to view this content.

hmm.. without seeing your code, i can't really be sure what's going on.

Can you copy out and past what you have?

Looks like you define HIT in the wrong place. Should be an easy fix


Basically, you need to move that DIM statement for the "hit" range variable to the Sub that is referencing that variable.

You have it declared in the Function findmatchrange() -- move the declaration (dim statement) for hit to whatever sub or function contains the line "For each hit in tempMatch". Notice where I have it declared in my previous example.


You are getting an error on that line because the variable is "out of scope" -- that particular sub or function just doesn't know what to do with it. Make sense?

Ok, I didn't realize I had it in both the sub and function. Just taking a guess for you, the message you posted sounds like one of the Range variables is not defined for that loop.

Without being able to step through the code I can only guess. Can you isolate what is going wrong? Step through the code and set a watch for those range variables, where are they pointing at that line (or are they not defined at that point)?

OK, so I set a watch on "hit" and "tempmatch" and stepped through the process. It looks like multiple comments are now being put into a cell, but I'm still getting the error. Here's what happens...I've numbered the lines of the last half only of the code you gave me for reference purposes:

Please Login or Register  to view this content.

1) Macro loops between lines 3 and 4 a few times. The values of tempmatch and hit all look OK during this process. The value of Hit seems to change with every iteration.
2) After doing this a few times, the script stops looping and continues on to line 6. Once it does this the Hit value is now empty.
3) Macro reaches line 7 and loops back to line 0. When it does this, the tempmatch variable now also becomes empty.
4) When the macro reaches line 2, it spits out the error. I assume this is because both variables are empty?

EDIT: I ran it with different data and then it doesn't loop at all. It just errors out when it reaches line 2

So, it seems like it's erroring out whenever it gets to line 2 and tempMatch is empty. Sometimes it loops once before that happens, sometimes it doesn't. Trying to narrow down the issue now. Will edit this post when I find more. It may be a specific piece of data input that's doing it.

Found the problem! If it looks for a value that isn't found, it errors out. The same thing happens in the example workbook you provided earlier.

It's working! I added the If statement

Please Login or Register  to view this content.

for everything after the Set tempMatch section. Thanks so much for your help!

EDIT: Looks like it's cutting 40-50% of the time off the original latency.

wow, thank you for all that.

Well, that's to be expected. So, what do we want to happen when there is nothing to be found? Leave the cell next to the number blank and move on?

We need to just point the thing to the next 'i' value when this happens.

I think this would work if we insert a couple of "on error" lines to gracefully move on when this happens:

Please Login or Register  to view this content.

on error goto skip - this allows us to skip the loop when there is no match (note, you have to name the line above the "Next i" skip