Thread: Count functions with variables

Hi Guys,

I have another request for help. I've been plugging away at my tool. (over 12,000 lines of code now). And I'm struggling with this next bit. The tool will generate a final report (I've attached a sample skeleton) and automatically creates the tabs, names them and formats them. It'll export the data into the "xxx Data" tabs. And I have been able to get it to automatically create the correct number of employees per site automatically.

Here's the hard part. You'll have to reference the "Prescott" or "PCT" tabs in the file to understand what I'm trying to do. (I had to strip it down so that it would upload, the file was over 3MB.)

Anyway, COLUMN A of the "PCT Detail" tab has a series of codes. If you look at COLUMN J of "DCT DATA" you will note that those codes correspond. I need a macro to automatically count the number of times each code is used and paste it in the appropriate column (So, For "Employee 1" it'll count them on the PCT Data sheet and then paste the number of times that code was used into Column B of the PCT Detail sheet. Employee 2 would have their results posted into Column D, etc.) Here's the catch, I need this macro do this dynamically. Not all sites have the same number of employees. I dont always know who's going to be listed in Row 2 at any given site, so I need the macro to figure it out all by itself. Frankly, I'm stymied. Any help would be appreciated.

Thanks!