consecutive negative numbers

[Roadhouse]

Hi can anyone help me with some vba.

I have a column in a spreadsheet column T that has positive and negative values, I want to count how many consecutive negative numbers before there is a positive number.

This will happen more then once so for example 7 negative numbers before a positive number.
Then 10 negative numbers before a positive number

I need to write the answers in column V for example
7
10

[JBeaucaire]

This seems like it does what you want:

Option Explicit

Sub SequentialNegatives()
Dim NegCnt As Long, RW As Long, LR As Long

For RW = 1 To Range("T" & Rows.Count).End(xlUp).Row
    If Range("T" & RW) >= 0 Then
        If NegCnt > 0 Then
            Range("V" & Rows.Count).End(xlUp).Offset(1).Value = NegCnt
            NegCnt = 0
        End If
    Else
        NegCnt = NegCnt + 1
    End If
Next RW

End Sub

[Roadhouse]

Hi thanks that works is there any way to ignore the blank cells when counting the negative numbers ?

[JBeaucaire]

This will ignore blank cells as it counts:

Option Explicit

Sub SequentialNegatives()
Dim NegCnt As Long, RW As Long, LR As Long

For RW = 1 To Range("T" & Rows.Count).End(xlUp).Row
    If Len(Range("T" & RW)) > 0 Then
        If Range("T" & RW) >= 0 Then
            If NegCnt > 0 Then
                Range("V" & Rows.Count).End(xlUp).Offset(1).Value = NegCnt
                NegCnt = 0
            End If
        Else
            NegCnt = NegCnt + 1
        End If
    End If
Next RW

End Sub

[Roadhouse]

hi that's great thank you for the help