Start and stop counting at the zero value

Naima - Jan 11, 2022 at 06:07 AM
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 - Jan 13, 2022 at 11:31 AM
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

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Jan 11, 2022 at 11:46 AM
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
0
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.
0
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.
0
NaimaHersi Posts 1 Registration date Wednesday January 12, 2022 Status Member Last seen January 12, 2022
Jan 12, 2022 at 12:28 PM
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.
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Jan 13, 2022 at 11:31 AM
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

0