Count number of zero occurrences

[Third Ricohermoso]

Hi,

Can someone please help me how to put a formula or a VBA code to count the number of occurrences a certain material will reach the ZERO status.

This is a monitoring database of our stocks and I would like to put a counter on the yellow column on the attached file, every time it will display a ZERO status. This will also help us to measure the capacity of our personnel to maintain the status of each material at GOOD level.

Hope you can help me with this.

A sample file is attached for your reference.

Thank you!

 

[Hui]

Add the following code to the Stock Level Monitoring module in VBA

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("I" & Target.Row).Text = "ZERO" Then
 Range("J" & Target.Row).Value = Range("J" & Target.Row).Value + 1
End If
Application.EnableEvents = True
End Sub

Save the file as a *.xlsm file type
enjoy

 

[NARAYANK991]

Hi Hui ,

Probably you can also have a Workbook_Open procedure to initialize the cells if they do not already have a non-zero value , and the status in column I is ZERO.

Narayan

 

[Hui]

I'm sure the logic I applied above is not comprehensive enough as it is
But it can be built on

 

[Third Ricohermoso]

Hi Sir Hui,

Thank you!
The code is very useful.
But I found out that the counter or the number on column J can be deleted manually, is there a way that this will not be deleted unless you reset the counter?

Also, if a reset button will be added, is it possible to be password protected such that I am the only one who can access it?

Thank you!

 

[Third Ricohermoso]

Hi Again,

I used the code given by Sir Hui and it was working properly.
Is it possible that whenever the counter counts it will record the number of days it stayed at zero thru the date today column and will stop counting at the date it was removed at the zero status?

My another problem is, what if the material reached the zero status more than once. I dunno how to reflect the number of days it reached zero for the second time.