How to make the macro run faster?

**ahng** · 09-13-2012, 11:33 AM

So I have the below code, it works but very slowly. What it does it that for each brokercode in the active workbook (let's called it #1), it searches the mainFile workbook for that value. Once found, it will apply the same color to the brokercode cell. Would anyone have a better idea to do this? I have 5000 entries and the macro sometimes freezes before giving the results. I'm not sure if it is because each brokercode in workbook #1 has duplicates, so there are many iterations doing the exact same thing over and over. Can anyone please help to make the macor run faster? I don't know how to do!

Please Login or Register  to view this content.

**HaHoBe** · 09-13-2012, 11:37 AM

Hi, ahng,

not switch between the files and instead of using a loop use either Find or WorksheetFunction.Match (maybe after having assured that there is a value with WorksheetFunction.CountIf). Do you need some code for these assumptions or can you go on with these on your own?

Ciao,
Holger

**ahng** · 09-13-2012, 11:48 AM

Could you help me with the code too? I'm not sure how to not switching between files. The mainFile workbook is a master fille and could only be read-only. It it is a list of brokers across all provinces and its column D is color coded by the broker's performance

Many thanks in advance,
Ann

**HaHoBe** · 09-13-2012, 12:11 PM

Hi, Ann,

code is untested but should be faster than your loop:

Please Login or Register  to view this content.

Regarding

The mainFile workbook is a master file and could only be read-only.

Dark clouds ahead - check for read-only prior to executing the code by

Please Login or Register  to view this content.

If true throw up a message (or save under a different name depending on how you would like to proceed).

Ciao,
Holger

**ahng** · 09-17-2012, 12:09 PM

Hi Holger,

Thanks a lot for your help. I just have time to check the code today. I kept getting error msg "Tyoe mismatch" for the line

Please Login or Register  to view this content.

I've checked and tried to modify a bit but still keep geting "Type mismatch" error. Could you please help? I'm sorry, I'm a very newbie for VBA

Thanks,
Ann

**HaHoBe** · 09-17-2012, 01:23 PM

Hi, Ann,

like I stated: code was untested (and with a lot of typos even in the variables).

Please give this code a try - I tested this one on a workbook and it worked fine there (I also deleted the Exit For which would have stopped the macro after the first found an not work on the other cells in the range):

Please Login or Register  to view this content.

Ciao,
Holger

**ahng** · 09-19-2012, 09:22 AM

I tested and it works perfectly. You're right, the "Exit For" was the problem. Last time, I fixed the typo in variables but didn't see the problem with the for loop.

Thanks a lot for your help!
Ann

**Cutter** · 09-19-2012, 11:48 AM

@ ahng

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.