Start and stop counting at the zero value

Report
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
Hello,
How to insert a COUNTIF function to count values once it reaches to the first zero value to stop. And count again the cells between the first zero and second zero. Any help will be appreciated


System Configuration: Windows / Chrome 97.0.4692.71

3 replies

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Naima,

Not sure if this can be done with a formula, but it definitely can with a macro code.

The code will start counting from A1 until a zero is found. Then the counting result is placed in the cell next to the zero (so in column B).

To better explain the result, here is a screenshot:


Here is the code:
Sub RunMe()
Dim mCount As Long

For Each cell In Range("A1:A" & Range("A1").End(xlDown).Row)
    If cell.Value <> 0 Then mCount = mCount + 1
    If cell.Value = 0 Then
        cell.Offset(0, 1).Value = mCount
        mCount = 0
    End If
Next cell
End Sub


Let us know if alterations are desired.

Best regards,
Trowa
Thank you very much Trowa. I have never used Macro code before. It is actually the first time I heard about. But I will definetly try to use it this time. I was wondering, if you also want to do the zero counting and leaving any values greater than zero.
Feedback.
I have tried and it worked well. Thank you very much. But again, I couldnot make my own code for counting only the zeros and leaving any value greater than zero. Your help will be very much appreciated.
Posts
1
Registration date
Wednesday January 12, 2022
Status
Member
Last seen
January 12, 2022

Dear Trowa. I have encountered slight problem. The result column has what is needed but also has zeros which was not meant to appear. How can I remove the zeros from the result column.
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Naima,

Great job making the code work. They can be intimidating the first time you encounter them.

From your feedback I get you want 2 additional things to happen:
1. Count the zeros in the source column.
2. No zeros in the result column.

Result will look like this:


Here is the code:
Sub RunMe()
Dim mCount, zCount As Long

For Each cell In Range("A1:A" & Range("A1").End(xlDown).Row)
    If cell.Value <> 0 Then mCount = mCount + 1
    If cell.Value = 0 Then
        zCount = zCount + 1
        If mCount <> 0 Then
            cell.Offset(0, 1).Value = mCount
            mCount = 0
        End If
    End If
Next cell

Range("A1").End(xlDown).Offset(0, 2).Value = zCount
End Sub


Feel free to ask if something in unclear.

Best regards,
Trowa