Macro to count specific cells on predicted excel file

[fcomino]

Hello all....

I will try my best to explain what I need as best as possible. This is what I have setup. Every day an Excel Spreadsheet gets saved manually as a specific name depending on the day and also in a specific folder depending on the date. For example. On the 28th February 2010. The file will be saved as

USMGT201002028_BL.xls
and will be saved in the following directory:
C:\UMR\2010\02 February

On the 1st of March this file name will change to USMGT20100301_BL.xls and will be saved in the folder C:\UMR\2010\03 March.... and so on for every other day.

This gets done manually every day. Now what happens is that the spreadsheet will be worked on by staff, and they work on it row by row, and basically they fill the entire row in RED when it is complete, and move to the next row.

What I need is a macro, that will be able to PREDICT what location to find the excel file, and also predict the excel file name (this all depends on the date) and then give me a count how many cells are not coloured in RED for that day.

So on the 28th February, I wanna know what rows are not marked in RED.

If there is no file by the predicted name, then I would like it to return 0.


Is this possible?

Thanks

[JBeaucaire]

Something like this:

Please Login or Register  to view this content.

[fcomino]

Thanks for that, that worked out great! however I have another step, if possible

Just to make things more complex....

In column A, the cells contain the following text
New Hire
Extend
Transfer
Secondment
Terminate

Is it possible to modify this macro to give me a breakdown of the following:
New Hire:
Extend:
ALL OTHERS:

Still following that the rule: only count when the row is not filled in red.

Thanks again

[JBeaucaire]

Try this:

Please Login or Register  to view this content.

[fcomino]

PERFECT!!!!! Your a gem!

Thank you Thank you!

[JBeaucaire]

If that takes care of your need, use the Thread Tools menu to mark the this thread as [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

[fcomino]

Hi I have been using the code above for the past few months however I have been getting stuck on 1 part.

If the file is currenlty open by someone, I get a popup message causing the macro to crash.

File Now Available

'Filename.xls' is now available for editing. Chose Read-Write to open it for editing.

Is there anyway I can avoid this popup?

[JBeaucaire]

Google to the rescue again. I found "test if file is open on the network" routine and it seems to do what you need. I added it into this code, there's much more of it now, so be sure to copy it all.

If the file is open, you will be told who has it open. If not, the macro will proceed normally since it is safe to do so.

Please Login or Register  to view this content.

(additional code source)