Count number of occurrences for a number of ranges from a find loop

Hello, I need to display the number of occurrences for each range within an array and put this value offset to the range cell. I have attached an example workbook of what I need to happen. The Macro (See below) works fine, just need to figure out how to count how many of each it finds. Just cant get my head around it? My Vba is very basic, hope someone can help?

Please Login or Register  to view this content.

Thanks

Sorry forgot to attach!!

A couple tweaks to your code:

Please Login or Register  to view this content.

I created a rngBins so I could refer to it later, and set it to the array of ranges you gave, with an extra column to store the totals. Then I set the totals to zero. Each item found increments the total for the given row. After all the sheets have been updated and accumulated, I go through the totals and use them to update column 1 of the array to Found/Not Found, and post it back to the worksheet using an offset to rngBins.

Thanks Wallyeye, thats great, you make it seem so simple, would never have been able to do that, works perfectly.
Hopefully not pushing my luck but is it possible to record the sheet names that the array values were found in and list them on the same sheet? In the actual workbook each sheet name is the name of a project, approximately 250 sheets at the moment, would be handy if the user could see a list of the projects (sheetnames) that had been added too. Doesnt matter which range was found on which sheet, just a list of the sheets that the ranges were found on! That would really top things off.

A couple more tweaks:

Please Login or Register  to view this content.

This starts the list two rows down from the bottom of the Bin Numbers, you can place it wherever you want. The scripting dictionary is a pretty cool tool I had pointed out to me recently, it works well in this application.

Hey Wallyeye, That is pretty cool. Ive just noticed the code fails if it finds nothing, rather than just showing not found and 0 for all! Any Ideas?

Wallyeye, I have another macro that runs on each worksheet when a cell is changed, but when this macro runs and adds the offset values to the sheet obviously the change macro does not activate. Is there a way to do this?

Just a little tweak at the bottom:

Please Login or Register  to view this content.

For the macros, I would recommend changing the worksheet_change macro on each to point to a generic routine, passing the changed cell, something like:

Please Login or Register  to view this content.

Then your Generic_Change (in a new module) would look something like this:

Please Login or Register  to view this content.

You would just need to make sure you don't refer to the active worksheet, and keep all your range or cell references related to the target.worksheet, i.e. instead of:

Range("A4")

you would use:

Target.Parent.Range("A4")

This will allow the Generic_Change routine to work with any worksheet.

Then, from the current Mark_Cells_In_Column procedure, after you update rng.Offset(0,1).value, you could just call Generic_Change with the desired target:

Please Login or Register  to view this content.

This would manually trigger the worksheet_change event when you procedure updates the cells.

Tweak works perfectly. Tried the rest, the generic routine works, so the code works as it did when it was running on the sheet, but only when i manually enter onto the sheet. When the cell values are added via running the Mark_cells_in_column macro it still doesnt seem to work, but does not errror! Its probably to do with my code?

Please Login or Register  to view this content.

The sheets are protected, so this adds a comment and then locks the cell once a value is entered.
I have attached the previous workbook, with all code in as an example.
Thanks for all your help, its much appreciated.

Sooo close. Generic_Change can be processed against any of the worksheets, so we need to be specific which worksheet we are referring to:

Please Login or Register  to view this content.

Here I've changed both of the protect lines to work on Target.Parent, replacing ActiveSheet. Then, for setting applicable_range, we need to look at Target.Parent.Range, to specifically get the Target's worksheet. "Range" by itself will refer to the ActiveSheet.

Bingo
Wallyeye, you have been a great help, thanks for everything, a true excel guru. I think its safe to save this post is well and truly solved.
Thanks again

Glad to be of help!