Revisit of Alternate zero vs non Zero Counter.

[louismk]

Hui,

About a month ago you sent me a very handy vba code that recapped the occurences of zeros in a given array of numbers into one cell. So like for example an array of 1-4-0-0-0-3-4-5 would return into one cell "2-3-3"- the "2" representing the 1 and the 4, the first "3" representing the next three zeros in the array and the second "3" representing the 3,4 and 5 in the array. The formula works great and I was able to successfully apply it to my last project but now I am in need of a simmlar macro that will count negetive values along with the zeros. So where the original macro that you sent to me counted zeros vs non zeros this new one I want to count values greater than zero vs values zero and less than zero. I have pasted the original macro at the bottom of this posting.

Cheers!


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

ElseIf myArr(i + 1) <> 0 Then

myStr = myStr + Str(count0) + "[color 3]"

End If

End If

Next


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


NumbZeros = myStr


End Function

 

[Hui]

Louismk

I think I checked all combination's but if you find a case that doesn't work let me know

[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 + Trim(Str(Count + 1))
Else
myStr = myStr + Trim(Str(Count)) + "-1"
End If
ElseIf myArr(i + 1) <= 0 Then
myStr = myStr + Trim(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 + Trim(Str(count0 + 1))
Else
myStr = myStr + Trim(Str(count0)) + "-1"
End If
ElseIf myArr(i + 1) > 0 Then
myStr = myStr + Trim(Str(count0)) + "-"
count0 = 0
ElseIf myArr(i + 1) <= 0 Then
' count0 = count0 + 1
End If
End If
Next

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

NumbZeros = myStr

End Function

[/pre]

 

[louismk]

Hui,

This new macro does the same thing as the first, it does not account for the negetives it is just counting zeros vs non zeros. I would think that the new one would be a simpler syntax don't you?- since all I want to do is count the sequence of posative values against the values that are less than or equal to zero. Is it just simply a change in the syntax? Thats what I originally thought but everytime I go in and try to play around with it it the function will not even work. Sorry- I hope that Im not bugging you.

 

[Hui]

It is working ok for me

[pre]

5	5	5	5	5	5	5	5	5	5		10
5	5	-5	-5	5	5	5	5	5	5		2-2-6
5	5	0	0	5	5	5	5	5	5		2-2-6
5	-5	5	0	5	-5	5	0	5	5		1-1-1-1-1-1-1-1-2
-5	5	0	5	5	5	5	5	5	5		1-1-1-7

[/pre]
It is counting Negatives and Zero together and Positives together

 

[louismk]

Hui,


You're right, this works fine, I messed it up in mine at first but now it is working just fine. Thanks alot! Also, I have another question. Do you know of a way to make an IF statement "adjustable" meaning that a user could easilly change it to the criteria that they wish it to be. Like for example I'm working on a project now that gives a ranking of "Excellent" to all records that score a "0" or a "1" in a given coloum. It then gives a ranking of "Good" to all records that have a 2-4 in that column. The rankings go all the way up to 13. It is just a simple "IF" statement that I made to get these rankings listed for each row. But what I want to know is if there is someone who may want to set the "excellent" ranking to be aquired by records scoring a 0,1,or 2 in that coloum as opposed to the 0 or 1 that I have in the IF statement- is there a way for them to do this easilly and with out haveing to change the syntax of a formula? I know that this probably sounds crazy because it pretty much defeats the whole purpose of my IF statement- but would you know of a way to go about acheiving this capibality in my project?

 

[Hui]

Loiusmk

You can do something like put a VLookup inside a Choose

So setup a table of scores in K1:M5

[pre]

Lower	Upper	Level
1	 2	 1
3	 4	 2
5	11	 3
12	15	 4
and the use a

=CHOOSE(VLOOKUP(C1,K2:M5,3),1,2,3,4)

Where you lookup a value from C1

Now you can change that to have text as

=CHOOSE(VLOOKUP(C1,K2:M5,3),"Excellent","VG","Poor",Appaling")


The user can change the values in K1:M5 to suit


or you can just use a VLookup

Same table with

Lower	Upper	Level
1	 2	 Excellent
3	 4	 Very Good
5	11	 Poor
12	15	 Appalimg

[/pre]
and use =Vlookup(C1,K2:M5,3,True)

where the user can change the values in K2:M5