Counting Loop - Please help

[Zachary Baker]

I'm working on a forecasting program for my Industrial Engineering Senior
Design group. We need a function that can count in the following way.

Given the column:

1
3
4
2
0
0
2
5
0
4

This program should return

1
1
1
1
0
0
3
1
0
2

What this program is doing is looking at each cell in the column. If the
cell is zero, the function will return a 0 for the corresponding cell and
look forward to the next cell. If it is zero again, it will return a 0 and
continue in the loop until it reaches a nonzero. Upon reaching a nonzero, it
will count itself and the number of zero cells before it and return that
value. If there is a nonzero cell proceeded by a nonzero cell, the function
will simply return one.

I know this sounds crazy but we are desperate for help.

Thanks.

-
Zachary

 

[Sheeloo]

Assuming your values are in Col A starting at A1 and there is nothing else on
the sheet then try this macro;
'--------------------------
Sub CountZero()
Dim i
Dim j
i = 0
j = 0
LastRow = Range("A65536").End(xlUp).Row
If ActiveSheet.Cells(1, 1) = 0 Then
ActiveSheet.Cells(1, 2) = 0
Else
ActiveSheet.Cells(1, 2) = 1
j = 1
End If

For i = 2 To LastRow
If ActiveSheet.Cells(i, 1) = 0 Then
j = j + 1
ActiveSheet.Cells(i, 2) = 0
Else
ActiveSheet.Cells(i, 2) = j
j = 1
End If
Next
End Sub
'--------------------------