Alternate counter between non zeros and zeros

[louismk]

What I am trying to do is show the number of non zeros vs the number of zeros in a given range. For example; I have a table that is about 1800 rows down and 13 coloums accross. The thirteen values in a given row could be something like: 1-6-0-0-4-5-2-8-4-2-0-0-0. What I want to do idealy would be to have something that could tell me in one cell the number of values vs zeros and the order that they occur in. So for the given range above I would want my return to be: "2,2,6,3". The first "2" would signify the 1 and the 6 in the range and the second "2" would signify the next tow zeros in the range. The "6" would signify the the 4-5-2-8-4-2 and the "3" would be the 0-0-0 at the end of the range. I would also want to color code the return so that the number representing the ammount of zeros counted in the range would return as red or something.

-Thanks!

 

[Hui]

Louismk

Have a go at the following user defined function

Copy and paste it into a code module in VBA

Use it by entering =NumbZeros(A1:A13)

===

[pre]

Function NumbZeros(myData) As String

Dim myArr
Dim myStr As String

On Error Resume Next

Count = 0
Count0 = 0
myStr = ""

Set myArr = myData

For i = 1 To myData.Count - 1

If myArr(i) <> 0 Then 'Data <>0
Count = Count + 1
If i + 1 = myData.Count Then
If myArr(i + 1) = 0 Then
myStr = myStr + Str(Count) + "-1"
Else
myStr = myStr + Str(Count + 1)
End If
ElseIf myArr(i + 1) = 0 Then
myStr = myStr + Str(Count) + "-"
Count = 0
End If
Else ' Data =0
Count0 = Count0 + 1
If i + 1 = myData.Count Then
If myArr(i + 1) = 0 Then
myStr = myStr + Str(Count0 + 1)
Else
myStr = myStr + Str(Count0) + "-1"
End If
ElseIf myArr(i + 1) <> 0 Then
myStr = myStr + Str(Count0) + "-"
Count0 = 0
End If
End If
Next

If Right(myStr, 1) = "-" Then myStr = Left(myStr, Len(myStr) - 1)

NumbZeros = myStr

End Function

[/pre]

 

[louismk]

Hui, This works awesome! Thank you very much. Would there be a way for me to easilly color code the numbers that represent the number of zeros? Other than that this is exactly what I am looking for.

Thanks again!