counting ranges on vba

[montecarlo2012]

Hi, everyone
I'm trying to count ranges between the same value
Here's what I have so far:

Sub jumping()
Set rngData = Range("B2:B11")
n = 0
m = 1
For Each cell In rngData
    If cell = 6 Then
        Range("H2").Offset(0, m) = n
        n = 0
        m = m + 1
    Else
        n = n + 1
    End If
Next
End Sub

I'm having trouble getting it to work properly.
My array example for short is:

I am trying to be simple!.
so the code return this

so until here is right, meaning, count only one cell in the column be, and this cell it is not equal to 6, then skip 2 not equal to 6 then etc.
so, what is my question here
If I have the target value in different cell in the range then this code do not count, let me show you

now like this, I try like
Set rngData = Range("B2:F11")
do not work, also like
Set rngData = Range("B2:F2 , B11:F11")
do not work
So please, anyway you can help me here.

 

[davesexcel]

Maybe count the 6's in the row.

Sub jumping()
    Dim a
    Set rngData = Range("B2:B11")
    n = 0
    m = 1
    For Each cell In rngData
        a = Application.WorksheetFunction.CountIf(Range("B" & cell.Row & ":F" & cell.Row), 6)
        If a > 0 Then
            Range("H2").Offset(0, m) = n
            n = 0
            m = m + 1
        Else
            n = n + 1
        End If
    Next
End Sub

 

[montecarlo2012]

Hello davesexcel, and thank you for your code, I think the way I explain here was a mistake, sorry is my bad, when I illustrated my array with all those x's I didn't realize could be taking literally
my array is all numbers and I was trying to see the behave of number 2 in this case, anyway your code work if the array is exactly my illustration but if it is full numbers then don't.
Can you please, can fixed for numbers, let me give you an examples:

4​	6​	8​	13​	28​
1​	9​	11​	17​	18​
2​	7​	8​	12​	19​
6​	11​	14​	23​	32​
13​	15​	22​	30​	36​
1​	7​	10​	12​	15​
4​	17​	18​	19​	29​
15​	20​	25​	27​	32​
4​	18​	19​	22​	36​
5​	6​	24​	34​	35​
2​	7​	12​	29​	33​
7​	10​	14​	28​	33​
4​	12​	17​	20​	23​
9​	17​	21​	26​	34​
5​	15​	21​	29​	31​
4​	5​	7​	19​	36​
12​	16​	24​	25​	33​

and here for example I would like to see the behave of number 7
so the output will be
2, 2, 4, 0, 5
by the way I like the name you got here.

 

[DanteAmor]

Hi @montecarlo2012:

2, 2, 4, 0, 5

Based on your example, the result should be as shown in the image below:

For the behavior of number 7.
If you want another number change it in this line:

Set f = Range("B" & i).Resize(, 5).Find(7, , xlValues, xlWhole)

Try this macro:

Sub jumping_v2()
  Dim i As Long, n As Long
  Dim f As Range
  For i = 2 To Range("B" & Rows.Count).End(3).Row
    Set f = Range("B" & i).Resize(, 5).Find(7, , xlValues, xlWhole)
    If Not f Is Nothing Then
      Cells(2, Columns.Count).End(1)(1, 2).Value = n
      n = -1
    End If
    n = n + 1
  Next
End Sub

Note XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Example:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		4	6	8	13	28	2	2	4	0	3
3		1	9	11	17	18
4		2	7	8	12	19
5		6	11	14	23	32
6		13	15	22	30	36
7		1	7	10	12	15
8		4	17	18	19	29
9		15	20	25	27	32
10		4	18	19	22	36
11		5	6	24	34	35
12		2	7	12	29	33
13		7	10	14	28	33
14		4	12	17	20	23
15		9	17	21	26	34
16		5	15	21	29	31
17		4	5	7	19	36
18		12	16	24	25	33
19
Hoja10

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[montecarlo2012]

Great job, thank you so much, and in the future I will be using XL2BB v2.0. sorry about that.
12 lines code, really amazing, the part I don't get it is, why if I want to do the same idea about the behavior of a number in that array, for example if I have on M2:M37 the numbers from 1 to 36, so
Set f = Range("B" & i).Resize(, 5).Find(7, , xlValues, xlWhole) in this line the 7 will be a variable, how is possible that most of the codes always expend more than 40 lines instead of maybe couple or three lines more.
anyway, you don't have to answer this, is just a comment.
Thank you DanteAmor and like I always say
"MVP Dante Amor for me"

 

[montecarlo2012]

This line is the best lesson today.
' Set the range variable "f" to the first cell that contains the value "4" in a range of cells starting from the current row in column "B" and extending 5 columns to the right.
Set f = range("B" & i).Resize(, 5).Find(4, , xlValues, xlWhole)
you make my day
thanks.

 

[montecarlo2012]

Sorry, collateral question.
in the first basic code I use to control where to start my output here
Range("K2").Offset(0, m) = n
so now in this code how can I do that. Please.

 

[montecarlo2012]

Hello, I figure out what I want
this is the line to change
Range("P2").Offset(0, n).Value = n
or if I want in a column will be
Cells(Rows.Count, "P").End(xlUp).Offset(1, 0).Value = n
homework done.
I do this because we never know who else have the same question.