Optimize Search & Import Macro

[Hyflex]

Hey Guys,

I'm looking for some help optimizing this macro to make it faster because it takes forever to run with my extremely large excel documents.

The macro looks at sheet one "ListNames" and looks in the cells under column A and uses the name and matches them with names in sheet two "Data" under column A if/when it finds a match it will use the offsets defined in the macro to import the data from "Data" to "ListNames"

It also has a commented out section to the macro where if the name does not exist in "Data"but does exist in "ListNames" it will add the missing names to "Data"

My "Data" sheet is updated once a periodically and has over 50 thousand lines of data, I ran a copy of this macro last week using a bunch of offsets and it took several hours to pull all the data so if anything can be optimized in this macro it would be GREATLY appreciated.


Attached a small example of the macro & here is a copy of the actual macro:

Please Login or Register  to view this content.

[tigeravatar]

Hyflex,

Instead of using a second loop to find the value, you can use the Range.Find method. Also, instead of writing each result to the ListNames sheet individually, you can load the results into an array and then write the end result all at once. Give the following a try:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

P.S.: As a side note, this code layout and logic looks extremely similar to coding I used to use before I knew about the .Find method or how to effectively use arrays

[Hyflex]

Hey, I just found a little problem and Im having trouble where two pieces of data contain similar text, or where one piece of text is listed in a different bit of text i.e. "Elizabeth" with her set of credentials and "Elizabeth Jordan" using these as a match to pull data from one sheet to another causes problems.

Also I need it to ignore certain words i.e. "Name" but again it needs to be a specific match.

Any help would be greatly appreciated
~ Hyflex

[tigeravatar]

Hyflex,

To get the .Find to ignore partial matches, set the LookAt property to xlWhole:

Please Login or Register  to view this content.

I'm not entirely sure what you mean here:

Also I need it to ignore certain words i.e. "Name" but again it needs to be a specific match.

Are there words in the list that you don't want it to search for? So if "Name" is in the list, you don't it searched for?

~tigeravatar

[Hyflex]

Oh wow you are awesome, thank you very much for the first bit & the cross-workbook version.

As for the second part, yeah I need it NOT to search for "Name" and blanks, I was trying to throw in some If's in saying if it is NOT blank and NOT "Name" then do... (and carry on) but I guess I was doing it wrong.

[tigeravatar]

Hyflex,

You're on the right track with the If statement, here's how it should look in order to not search when coming across a blank value or "Name":

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[Hyflex]

I can't seem to get the exact match part working...

Please Login or Register  to view this content.

"Run-time error '91':
Object variable or With block variable not set"

[tigeravatar]

Hyflex,

The only way to get that error on that line is if wsData isn't defined. In the example workbook, wsData is set to Sheets("Data"). I noticed you mentioned the pulling from a different workbook. If that's the case, wsData may not be set to the correct worksheet. Sheets with no object preceding it will assume the currentworkbook. You may have to specify the workbook you want and then .Sheets. For example:

Please Login or Register  to view this content.

~tigeravatar

[Hyflex]

Yeah, I had done that. I just found my error. I forgot to change Sheets("Data")

EDIT: Error once it gets to the:

Please Login or Register  to view this content.

Subscript out of range

[tigeravatar]

HyFlex,

Before you edited your code away, you set the array dimensions with the following:

Please Login or Register  to view this content.

That results in an array of two columns, A:B. So the second dimension of the array cannot be higher than 2. But, when you've found the value, you try to put it in column BI (61):

Please Login or Register  to view this content.

Since the second dimension of the array only goes up to 2, there is no 61, which is why you're getting the subscript out of range

Hope that helps,
~tigeravatar

[Hyflex]

hank you so much, this macro works flawlessly and is so much faster than the original, like 1000% faster

[Hyflex]

I'm wondering if anything else can be down to the macro below, I have an idea but I have no idea if this is possible.

I first of all need it do do basically the same function but match the names the other way round, so it will use the names from "Data" and match the names to the ListFull sheet, on a matched name it will pull the data from "Data" and put it into the ListFull sheet

My "ListFull" sheet contains 300,000+ names, would it be possible to make this macro sort alphabetically and then over to the right it to remember row numbers...

So for example in
AA1 it has the number 5 (Start Row)
AA2 it has the number 8 (First Name starting with B)
AA3 it has the Number 19 (First name starting with C)

Then when it's running the macro when it finds a name it can start looking for the name faster... in a smaller section. So if the macro looks in "Data" and finds "Benny" then it looks for that name in "Data" it will know to look in rows 8 to 19 for the correct name

Please Login or Register  to view this content.