How to improve performance of my code?, now is too slow!!

Try this:

Please Login or Register  to view this content.

Hi laurelb,

Welcome to the Forum!

Here's another to try:

Please Login or Register  to view this content.

Instead of using 2 nested loops to compare every SAP# on sheet 1 with each SAP# on sheet 2, I would suggest using one loop that examines each SAP# on sheet 1 and then use MATCH to see if it exists on Sheet 2. If so, set the result for 'Listed' on sheet 1 to 1, otherwise to 0.
This should save MANY unneeded comparisons and speed up the macro considerably.

Something like this.

Please Login or Register  to view this content.

- Stu

See next Code

Please Login or Register  to view this content.

Some questions:
why to start on 19 in sheet2?
ColNum is a number of rows?
Is it really slow ? not on my PC
Now if it is still sloow , it can be faster, do we need

Why are you using VBA when the much simpler =MATCH() function directly in Sheet 1 column L would produce your answer.

Never use VBA when standard Excel functionality is available. VBA is always more time consuming particularly when loops are involved.

Use in L8

Formula:

Please Login or Register  to view this content.

Never use VBA when standard Excel functionality is available. VBA is always more time consuming particularly when loops are involved.

I don't think so

Originally Posted by xladept

I don't think so

Hi Richard,

I've had occasion to straighten out calculation bound spreadsheets and, with any serious volume, the VBA solution is always quicker.

I know that's contrary to popular opinion, but it's true nonetheless

"I know that's contrary to popular opinion, but it's true nonetheless"
I fully agree, with my experience, macros are more efficient because doing only what must be done.
Certainly maintenance is another issue ...

Certainly maintenance is another issue ...

To my senses, a worksheet full of formulas is just plain messy and easy to mess up

Originally Posted by PCI

I fully agree, with my experience, macros are more efficient because doing only what must be done.

..except that I've lost count of the number of times questions are asked here about slow macros which contain loops when standard Excel functionality and functions are light years faster and should be used instead of VBA

The operative words are 'doing only what must be done'. The problem with looping macros is that the loop conditions have to be evaluated before you can even begin to decide whether to process any data. The very act of evaluation more often than not means testing something in the application and of course every time you jump backwards and forwards between VBA and the Excel Application there is a time overhead which really slows things down when large ranges are being processed.

I really don't understand this pre-occupation with macros. Don't get me wrong, I agree they are extremely powerful and sometimes the only way to do something, but the operative word is 'sometimes'. Mostly and IMO they certainly aren't the first port of call for processing data.