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.

 

[Zot]

I'm confused. Wasn't P4 = 0?

 

[johnnyL]

Sorry, my brain is mush at this time of the morning. The following is what I have come up with for the first 4 parts mentioned in the Original Post:

Sub AA()
'
'   this code count the interval between the same values
'
    Dim m       As Long, n  As Long, RowNumber  As Long
    Dim cell    As Range
'
    For RowNumber = 2 To 37
        For Each cell In Sheet2.Range("U" & RowNumber & ":HH" & RowNumber)
            If RowNumber Mod 2 = 0 Then                                                             ' If Even row number being checked then ...
                If cell.Value = 2 Then                              '////////////////// ("P2")      '   If even Row number and cell.value = 2 found then ...
                    Sheet7.Range("C" & RowNumber).Offset(0, m) = n
'
                    n = 0
                    m = m + 1
                Else
                    n = n + 1
                End If
            Else                                                                                    ' If Odd row number being checked then ...
                If cell.Value = 1 Then                              '////////////////// ("P3")      '   If odd Row number and cell.value = 1 found then ...
                    Sheet7.Range("C" & RowNumber).Offset(0, m) = n
'
                    n = 0
                    m = m + 1
                Else
                    n = n + 1
                End If
            End If
        Next
    Next
End Sub

See if that works.

 

[montecarlo2012]

Sorry guys the way I putting.

Start point is (“U2”), I am checking the behave of the value on (“P2”)

Let see

U2 the value on P2 it’s not there, so 1 skip,

V2 the value on P2 it’s not there, so 2 skips,

W2 the value on P2 is there, so this is not skip

So keep going

X2 = skip - 1

Y2 = skip - 2

Z2 = skip - 3

AA= skip - 4

AB = skip - 5

AC = skip – 6

All this happen on sheet 2 (reading)

So the output on sheet 7 (“C2”) will be 2 – 6 and after here 0

Because P2 value is again there.



So the code in the place for the condition must have a variable

||||||| If cell = 2 Then [[[[[ ("P2")

You see, the next time will be P3, and so on



And happen the same thing on



Set rngData = Sheet2.Range("U2:HH2") this is the first line to read and after

Set rngData = Sheet2.Range("U3 : HH3")this one will be the next etc.



I hope I am a little better this time, sorry.

 

[bebo021999]

Thinking of Find method
But now I have to leave, will come back soon.

 

[montecarlo2012]

Sub AA()

Set T = Sheet2.Range("U2:HH2")


For Q = 2 To 37

            For Each cell In T
                        If cell = Q Then
                        
                           Sheet7.Range("c2").Offset(0, m) = n
                            n = 0
                            m = m + 1
                            
                        Else
                            n = n + 1
                        End If
                
            Next

Next Q

End Sub

This is my new attempt, work for one line, but do not run for the rest
any sugestion
thanks

 

[johnnyL]

@montecarlo2012 do a macro record for a few consecutive lines results that works and post the results. That might help us help you.

I ask that because the code you just posted is very similar to post #3 in this thread.

 

[Zot]

Sub AA()

Set T = Sheet2.Range("U2:HH2")


For Q = 2 To 37

            For Each cell In T
                        If cell = Q Then
                       
                           Sheet7.Range("c2").Offset(0, m) = n
                            n = 0
                            m = m + 1
                           
                        Else
                            n = n + 1
                        End If
               
            Next

Next Q

End Sub

This is my new attempt, work for one line, but do not run for the rest
any sugestion
thanks

This was what I thought you were trying to do but not really sure since you did not reply to my question.

Seems to me that you are comparing with values in column P, but your code is comparing to Q value which is 2, 3, 4, 5, and so on. You also mentioned that the column number is dynamic. So, it should be like this. I hope this is what you were trying to do

Sub AA()
'this code count the interval between the same values

Dim nRow As Long
Dim ws2 As Worksheet, ws7 As Worksheet
Dim rngData As Range

Set ws2 = ActiveWorkbook.Sheets("Sheet2")
Set ws7 = ActiveWorkbook.Sheets("Sheet7")

Set rngData = ws2.Range("U2", ws2.Cells(2, Columns.Count).End(xlToLeft))

For nRow = 2 To 37
    For Each cell In rngData
    If cell = ws2.Range("P" & nRow) Then
        ws7.Range("C" & nRow).Offset(0, m) = n
        n = 0
        m = m + 1
    Else
        n = n + 1
    End If
Next

End Sub

 

[bebo021999]

Sub test()
With Sheets("sheet2")
Lr = .Cells(Rows.Count, "P").End(xlUp).Row
For r = 2 To Lr 'Start from P2 to P3,P4,...
k = 0
s = .Range("T2").Column 'very first column index
    For c = .Range("U2").Column To Range("A:HH").Columns.Count ' c from 21 to 216
        If .Cells(r, c) = .Cells(r, "P") Then
        k = k + 1
        Sheets("sheet7").Cells(r, k + 2).Value = c - s - 1
        s = c
        End If
    Next
Next ' Next P3,P4,...
End With
End Sub

 

[montecarlo2012]

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.