Looping through CountIfs very slow using VBA

[newbi004]

Hi

I have the following code to loop though data to perform a series of CountIfs and fill specific cells with the results. However, this is very slow with 100 rows of data and I am concerned that it will be even slower when the number of rows become hundreds or even several thousand.

Could someone suggest a way to improve performance? I was wondering if arrays could be the answer?

The principle is that all the data is in Sheet4. My criteria is to count the number of occurrences the Department 1 to Department 6 against each of the codes LFT, CAV, EXO, EXL, EXC and REM.

The code that I have at the moment (which works very slowly!) is :

Please Login or Register  to view this content.

Thanks

[Norie]

There's no looping in that code.

Have you tried using a pivot table to get the results you want?

In the pivot department would be a row field, code a column field and count of code a data field.

[newbi004]

You are right, Norie. It doesn't loop! Just hard-coded.

Of course, pivot tables will be the answer. I will try that avenue.

Thanks for your reply

[berlan]

Here's a looping alternative (untested):

Please Login or Register  to view this content.

[newbi004]

Berlan

Sorry about the delay in getting back. I was testing your code and it took me a bit longer because you have introduced me to a new vba coding with Choose. I didn't know about that. Thanks for taking the time to help me.

Your code has made the procedure much more compact and incorporated a 'proper loop'. It was much faster than mine.

Cheers