Counting to the end of the array.

[montecarlo2012]

Hi everyone.
this code

Sub jump()
    Dim i As Long, n As Long
    Dim f As Range

    For i = 2 To Range("B" & Rows.Count).End(3).Row
                               'target value ( 1 )
        Set f = Range("B" & i).Resize(, 5).Find(1, , xlValues, xlWhole)

        If Not f Is Nothing Then
              
            Range("K" & Rows.Count).End(xlUp).Offset(1, 0).Value = n

            n = -1
        End If

        n = n + 1
    Next

End Sub

is doing good to certain point.
because is counting the "Rows" where the target value " 1 " is not present
but, when arrived to the last location of number one then do not count anymore
so what I want is to count
also after the last one to the end of the array
I am loading a picture that maybe show you better the idea

as you see is perfect the count of 3,9 "0's if repeating" and 1
and the code stop right there,
I really need the last part count also, meaning in this case
must be 6 rows where number one is not present also.
Please any help.
Thank you for reading this.

 

[bebo021999]

Book1.xlsm
	B	C	D	E	F				J	K
2	22	8	9	2	19					3
3	18	5	27	24	7					9
4	28	17	6	9	13					0
5	1	5	3	2	4					1
6	4	12	9	30	28					6
7	4	29	30	21	9
8	14	17	10	29	12
9	2	11	29	12	13
10	21	19	12	4	21
11	2	17	19	27	3
12	22	19	27	25	2
13	15	24	5	4	21
14	10	20	27	15	12
15	1	2	21	12	24
16	1	19	29	29	5
17	20	2	3	8	6
18	1	19	18	21	9
19	18	30	20	14	30
20	2	13	3	17	23
21	6	15	18	18	12
22	4	17	8	27	15
23	30	5	17	19	13
24	5	27	4	6	17
Sheet1

Try below code:

Sub jump2()
Dim lr&, i&, j&, k&, lastF&
Dim rng, res(1 To 10000, 1 To 1)
Const searchNum = 1
lr = Cells(Rows.Count, "B").End(xlUp).Row
rng = Range("B2:F" & lr).Value
For i = 1 To UBound(rng)
    For j = 1 To UBound(rng, 2)
        If rng(i, j) = searchNum Then
            k = k + 1
            res(k, 1) = i - lastF - 1
            lastF = i
            Exit For
        End If
    Next
    If i = UBound(rng) Then res(k + 1, 1) = i - lastF
Next
Range("K2:K1000").ClearContents
If k > 0 Then Range("K2").Resize(k + 1, 1).Value = res
End Sub

Explain:

1. It defines a subroutine named "jump2" without any input parameters.
2. It declares and initializes several variables: lr (representing the last used row), i, j, k (all as Long data type), and lastF (as Long data type).
3. It declares a variant variable named "rng" and an array variable named "res" with dimensions 1 to 10000 and 1 to 1.
4. It sets a constant named "searchNum" to the value 1.
5. It assigns the last used row number from column B to the variable "lr".
6. It assigns the values from the range "B2:F" concatenated with the last used row number to the variable "rng".
7. It starts a nested loop using the variables "i" and "j" to iterate over the values in the "rng" array.
8. Inside the loop, it checks if the current value in the "rng" array (rng(i, j)) is equal to the "searchNum" constant.
9. If a match is found, it increments the "k" variable, stores the difference between the current row and the previous found row (i - lastF - 1) in the "res" array, updates the "lastF" variable to the current row, and exits the inner loop.
10. If the loop reaches the last element of the "rng" array (i = UBound(rng)), it stores the difference between the current row and the previous found row (i - lastF) in the "res" array at the next position.
11. It clears the contents of the range "K2:K1000".
12. If at least one match was found (k > 0), it assigns the values of the "res" array to the range starting from "K2" and resizing it to the size of the "res" array.

In summary, this code is searching for occurrences of the number 1 in the range "B2:F" and calculating the number of rows between each occurrence. The results are then stored in the range "K2:K1000" in the worksheet.

 

[Peter_SSs]

I am loading a picture that maybe show you better the idea

Your picture does raise a question since in it, the only "1" values are in the first column yet your code is looking across 5 columns each time.
So, are all the "1" values always in the first column?
If not then please give us a new, more representative, set of sample data and the expected results, including if it is possible to have more than one "1" in the same row.

Also, are the values in your data the result of formulas or just numerical constants?

 

[montecarlo2012]

bebo, great, great job not only the code, you spend a little more time and explain your code that, is very nice of you, thank you for that, really help even more.
you are awesome.

 

[montecarlo2012]

Peter_SSs, Hello how are you,
I really appreciate you question, I am more than happy you ask about it,
and yes you are right, looks like everything is about one only,
but No,
I just don't want to be so complicate, and maybe for that reason nobody want to help me
but is about for me that I can change the value
in this line
Set f = Range("B" & i).Resize(, 5).Find(1, , xlValues, xlWhole)
and then I am able to plug in anything
in this case I am doing an study of 36 numbers
an for the example number one of course is just in the first column
but if I want for example the number 7 then this number can be anywhere
first column or second etc, and the same apply for the rest.
and thank you again you show me your interest in my question.

 

[montecarlo2012]

and sorry for the last part
they are just random numbers.
I would like to know if there are any pattern here

 

[montecarlo2012]

My real dynamic array have 10000 rows

 

[bebo021999]

and sorry for the last part
they are just random numbers.
I would like to know if there are any pattern here

Change this line:

Const searchNum = 7

If you want to search "7"
or using input box

searchNum = inputbox(" what number to search")

 

[Peter_SSs]

Thanks for the additional information. Given what you have said, I would go with code as @bebo021999 has suggested.
What I had in mind would not really be suitable for that much data and when the searched for value could be in any of the columns.

 

[montecarlo2012]

Thank you guys,