COUNTIF VBA problem

[upmb851]

Dear all,

I want to use VBA to tackle the following problem:

I have 2 columns, the first column has values from 1 to 3 and the last column consists of ones and zeros. Now I'm looking for a code thats works in the following manner: The code needs to return "+1" every time that it detects a value of "1" in the second column AND the value of the first column is lower than the first column value of the previously detected "1". Note that if the second column consists of all zeros and just a single value of "1" the code needs to return a value of "0". Hopefully my question becomes clear with 2 simple numerical examples:

1	1
2	0
1	0
2	0
3	1
1	1
2	0
3	1

The code now needs to return a value of "1" since only row 5 and 6 meet the above described restriction.

1	0
2	1
1	1
2	0
3	1
1	0
2	1
3	0

The code now needs to return a value of "2" since row 2 and 3, and row 5 and 7 meet the above described restriction.

It would be great if anyone could help me out with this problem.

Cheers, Raymond

[alvin-chung]

Hi Raymond, assume the 2 columns are "A" and "B" following code will pop-up the result in message box:

Sub test()
    Dim rng As Range
    Dim prevNum As Integer: prevNum = 1
    Dim cnt As Integer: cnt = 0
    
    For Each rng In Range("A1:A8")
        If Cells(rng.Row, "B").Value = 1 Then
            If Cells(rng.Row, "A").Value < prevNum Then
                cnt = cnt + 1
            End If
            prevNum = Cells(rng.Row, "A").Value
        End If
    Next rng
    MsgBox cnt
End Sub

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[alvin-chung]

Hi Raymond, thanks for the repu