How can I count consecutive cells with positive or negative values in?

[Albert_]

Dear Experts,

I have a table with daily values that are updated Monday to Friday.

I would like to have a colum where I can see how many consecutive days with positive values or negative values are in.

The table that I work with is daily updated with positive and negative values .

In a separate column I would like to see how many consecutive days with negative or positve values has this week/month.

Only consecutive days.

i.e. if Monday =0.3,Tuesday= 1.3,Wednesday =0.7 has positive values in a separate column should reflect as = 3

How can I count the consecutive days with positive or negative values in?

Any help will be very appreciated.
Thank you.

 

[MickG]

For Data in column "A", try this for results in column "B"

[COLOR=Navy]Sub[/COLOR] MG27Sep55
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] cc [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Dn > 0 [COLOR=Navy]Then[/COLOR]
         c = c + 1
    [COLOR=Navy]Else[/COLOR]
         [COLOR=Navy]If[/COLOR] Not c = 0 [COLOR=Navy]Then[/COLOR] Dn.Offset(-1, 1) = c & " +"
       c = 0
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]    If[/COLOR] Dn < 0 [COLOR=Navy]Then[/COLOR]
        cc = cc + 1
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]If[/COLOR] Not cc = 0 [COLOR=Navy]Then[/COLOR] Dn.Offset(-1, 1) = cc & " -"
        cc = 0
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]If[/COLOR] c > 0 [COLOR=Navy]Then[/COLOR]
    Rng(Rng.Count).Offset(, 1) = c & " +"
[COLOR=Navy]ElseIf[/COLOR] cc > 0 [COLOR=Navy]Then[/COLOR]
    Rng(Rng.Count).Offset(, 1) = cc & " -"
[COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[Albert_]

Thanks Mick!
I'll try your suggestion.
Regards.