Start and stop counting at the zero value
Closed
Naima
-
Jan 11, 2022 at 06:07 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jan 13, 2022 at 11:31 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jan 13, 2022 at 11:31 AM
Related:
- Zcounte
- Start survey - Download - Horror
- Start up sound changer - Download - Customization
- How to start a listening party on spotify pc - Guide
- Start steam in big picture mode - Guide
- Start money cs 1.6 - Guide
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jan 11, 2022 at 11:46 AM
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:
Let us know if alterations are desired.
Best regards,
Trowa
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
NaimaHersi
Posts
1
Registration date
Wednesday January 12, 2022
Status
Member
Last seen
January 12, 2022
Jan 12, 2022 at 12:28 PM
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.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Jan 13, 2022 at 11:31 AM
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:
Feel free to ask if something in unclear.
Best regards,
Trowa
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
Jan 12, 2022 at 04:46 AM
Jan 12, 2022 at 11:57 AM
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.