Searching for sheet, counting and filling in data

I have this challenge I would like some help with.
After thinking about the task at hand, I found that I do not have the right skills with VBA to get it down.

Please see the attached file.

On sheet 1, the rows represent the date in a month, and the columns represent other sheets in the workbook, with the name of the sheet in the header.
On the sheets that have a name similar to the headers, B1:D1 in the example, there are columns with month, day and hour.

I would like a subroutine that does something like this:

1. Loop trough row 1 on sheet1 and check if it finds a sheet with a name corresponding to the value in the checked cell.
2. If it finds a sheet - count the number of lines of each day in that sheet (1, 2, 3 [...])
3. Fill in the count of items per day in the corresponding cell matching the column for the respective sheet name and the row of the counted day.
4. When it is finished with the first column that matches, start from step one and repeat for each matching sheet.

I would also like to be able to add more columns that correspond to a sheet name in Sheet1 at a later stage.

Thank you so much for your help in advance!

Best regards,
Knut

Originally Posted by Mordred

There's no doubt a more efficient way of doing this but I think it does what you require it to do.

Hi Mordred, you're completely right. This does what I require it to do. Thank you very much!

But after trying your formula I realized that it should also set 0 if the count is 0. That does not seem to happen right now.
Probably an easy fix?

/Knut

I'm so terribly sorry, Mordred. It was not intentional to not acknowledge your efforts straight away. I take pride in being generous with offering thanks and praise whenever someone is helping me out, but I realize that I've not been that good at it here at these forums. Please accept my sincerest apologies. I will promise work on my not desirable habits - you guys at this forum are really passionate about what you do, and you deserve proper credits for persevering with people like me, that are complete novices when it comes to VBA. The help and efforts you and the other users at this forum put into other persons challenges and problems with Excel really make this a thriving community, where it is easy to get assistance when needed. And that is something I really appreciate.

With regards to your proposed solution, I have unfortunately not had time to test it yet. I will implement it and test it tomorrow, and get back to you as soon as it is tested.

Now Mordred, I've had the chance to test your code. It did everything I wanted, and then some. A bug caused it to fill 0 into the entire column to the left of the column being processed.

I changed the last for each statement, so that it will only affect the desired column. I went from:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

I would not have figured this out without your help! Thank you so much!

I include the final version of the routine here, for others reference:

Please Login or Register  to view this content.