vba. counting skips.

[montecarlo2012]

Hi.

Sub AA()
'this code count the interval between the same values
Set rngData = Sheet2.Range("U2:HH2")
For Each cell In rngData
    If cell = 2 Then   '////////////////// ("P2")
        Sheet7.Range("c2").Offset(0, m) = n
        n = 0
        m = m + 1
    Else
        n = n + 1
    End If
Next
End Sub

This code count how many cells the same value jump
The start point to count is “U2”
So the first value to start the count is at Column P2
have the value 2 so the code count from “U2” to the end
all this table change daily (dynamic array).
My little code help me with one line at the time.
I have been trying to figure out how to plug variable that change
In all this changes, but not clue.

I repeat my code already four times in order to illustrate more or less
What I am looking for, and to indicate what parts are changing as well
The size is 36 rows by any number of columns

When I rung the first code this is what I got

The value on P2 is 2 so if you start to count from U2, the number two is two spaces apart, and after 6 and so on, the same with the rest of the P values.
this four results is because I just copy and paste the same code four time, and change the necesary part for this task.

Sub AA()
'this code count the interval between the same values
Set rngData = Sheet2.Range("U2:HH2")
For Each cell In rngData
    If cell = 2 Then   '////////////////// ("P2")
        Sheet7.Range("c2").Offset(0, m) = n
        n = 0
        m = m + 1
    Else
        n = n + 1
    End If
Next
End Sub
Sub AB()
Set rngData = Sheet2.Range("U3 : HH3")
For Each cell In rngData
    If cell = 1 Then   '//////////////////("P3")
        Sheet7.Range("c3").Offset(0, m) = n
        n = 0
        m = m + 1
    Else
        n = n + 1
    End If
Next
End Sub
Sub AC()
Set rngData = Sheet2.Range("U4 : HH4")
For Each cell In rngData
    If cell = 2 Then   '//////////////////("P4")
        Sheet7.Range("c4").Offset(0, m) = n
        n = 0
        m = m + 1
    Else
        n = n + 1
    End If
Next
End Sub
Sub AD()
Set rngData = Sheet2.Range("U5 : HH5")
For Each cell In rngData
    If cell = 1 Then   '//////////////////("P5")
        Sheet7.Range("c5").Offset(0, m) = n
        n = 0
        m = m + 1
    Else
        n = n + 1
    End If
Next
End Sub

so what change here:

Set rngData = Sheet2.Range("U2:HH2")
Set rngData = Sheet2.Range("U3 : HH3")
etc.

and also
If cell = 2 Then '////////////////// ("P2") the value is on P2 ...
If cell = 1 Then '//////////////////("P3")
If cell = 2 Then '//////////////////("P4")
and so on
I tried to create a loop for this but do not work, of course, I did wrong.

I couldn't figure out the loop for this or make it shorter.

Thanks for reading this.

 

[montecarlo2012]

Bebo your code work perfect thank you. I already click as solution

 

[montecarlo2012]

See you soon guys on the next post. Thanks for your time.

 

[Zot]

Thank you Zot, but I got an error at ►ws7.Range("C" & nRow).Offset(0, m) = n◄
one next missing but that nothing really, thank you.

Opsss...

I did not really test the code. I was just copying your original code since you said it was working fine for one loop. ?

My main objective is to show how you can make dynamic number of column since you mentioned about it being dynamic if I understood it right. Another is how to loop the row. The loop within the loop is purely your code since I don't really understand your requirement.

Anyway, glad you got it solved.

 

[montecarlo2012]

Zot I am so glad to see you around here, and thank you very much for your input, and yes was a lesson for me.