MsgBox doesn’t give a value

Question

In the column “V34:V99” there are digits 1,2,3,0 in the scattered order . I want to count how many situations will be (n) (three consecutive 1 к=3, without taking into account zeroes) ,if 2 or 3 occur , then k=o . I wrote the code but it doesn’t work (doesn’t give a number of situations (n)).

Code

Sub а33условие3()

    Dim k, n As Integer
    Dim parRange As Range

    Set parRange = Range("V34:V99")
    k = 0
    n = 0
    For Each Cell In parRange.Rows

        If Cell.Value = 1 Then
            k = k + 1
            If k = 3 Then
                n = n + 1
                k = o
                MsgBox n
           End If
        End If
        If Cell.Value = 2 Or 3 Then
            k = 0
        End If
    Next Cell  

End Sub

Do you want to check values in `V34:V99` range or in full rows? Because now you are checking full rows (`parRange.Rows`). If this is correct, then I think by using `For Each Cell In parRange.Rows` you get cells by columns, i.e. `A34, A35, A36,...,A99, B34, B35,...`. — Egan Wolf, Jun 13 '17 at 07:42
Also you can avoid an extra if statement by replacing `End If, If Cell.Value = 2 Or 3` by `ElseIF Cell.Value = 2 Or 3`Also I'm not sure if the Or statement works like this, or that you should write `Cell.Value = 2 Or Cell.Value = 3` — Luuklag, Jun 13 '17 at 07:51
@Luuklag , Cell.Value = 2 Or Cell.Value = 3 helps , now all works . Thanks a lot ! — maxim465, Jun 13 '17 at 08:01

score 0 · Accepted Answer · answered Jun 13 '17 at 09:49

You can't use the Or statement in this way. You should useCell.Value = 2 Or Cell.Value = 3 Also you can save yourself an If statement, see updated code below:

Sub а33условие3()

Dim k, n As Integer
Dim parRange As Range

Set parRange = Range("V34:V99")
k = 0
n = 0
For Each Cell In parRange.Rows

    If Cell.Value = 1 Then
        k = k + 1
        If k = 3 Then
            n = n + 1
            k = o
            MsgBox n
        End If
    ElseIf Cell.Value = 2 Or Cell.Value = 3 Then
        k = 0
    End If
Next Cell  

End Sub

MsgBox doesn’t give a value

1 Answers1