Code does not always return the right answer.

[montecarlo2012]

Good afternoon, everybody, I hope you got a good weekend.

Sub Distance_one()

Set rngData = Range("B2", "B40")


   For Each cell In rngData
      If cell = 5 Then
         Range("E2").Offset(0, m) = n
            n = -1
            
            Else
            n = n + 1
      End If
   Next

End Sub

This code counts how many rows each number from 1 to 36 is separated from line one.
My problem here is, the code is working, but, gives me the wrong results on certain numbers
and the right results on others.
I tried on many ways, tested many ways but still, right and wrong results.
Please, I need your help.

Book1
	A	B	C	D	E
1	SCALE	PERS
2	1	2			2
3	2	5
4	3	1
5	4	5
6	5	3
7	6	10
8	7	10
9	8	3
10	9	6
11	10	15
12	11	1
13	12	11
14	13	10
15	14	11
16	15	11
17	16	7
18	17	2
19	18	2
20	19	7
21	20	23
22	21	7
23	22	2
24	23	1
25	24	3
26	25	1
27	26	2
28	27	8
29	28	9
30	29	11
31	30	10
32	31	20
33	32	8
34	33	8
35	34	17
36	35	5
37	36	12
38	37	13
39	38	9
40	39	5
41	40	13
Sheet1

An example of wrong is when I plug number 2, in this scenario the right result would be "0", and the code return 2.
An example of a right is when I plug the number 13, in this case, the right result would be 36, and the code returns 36

Comments on the code are more than welcome.
thank you

 

[Micron]

What are m and n; undeclared variables (if they are, then their data type is variant)?
It's always my practice to use Option Explicit.
Nor have you declared rngData (or at least showed that) and I'd expect Excel to think that Cell is a variable as well.

Aside from that, I can't make sense of this

An example of wrong is when I plug number 2, in this scenario the right result would be "0", and the code return 2.
An example of a right is when I plug the number 13, in this case, the right result would be 36, and the code returns 36

 

[Micron]

OK, I think I get it now. But for 2 the correct answer would be 26? For 13 it would be 37? Your code only enters a value one time in E2 yet there are often several instances of the number being searched. So is this supposed to put a value from A in each row where found? Probably doesn't because m is always zero.

how many rows each number from 1 to 36 is separated from line one.

You said the right answer for 2 would be 0 but 2 is 1 line removed from line 1?

 

[Micron]

I was considering the "first" row to be row #1 which contains your headers. Ignoring that, then maybe this is what you need:

Sub Distance_one()
Dim rngData As Range, cel As Range
Dim m As Integer, n As Integer

Set rngData = Range("B2", "B40")
Application.EnableEvents = False
For Each cel In rngData
   If cel = 5 Then '<<why not use a cell value or input box for the value instead of modifying code for each value tested??
      Range("E2").Offset(m, 0) = m
   Else
       n = n + 1
   End If
   m = m + 1
Next
Application.EnableEvents = True
End Sub

 

[montecarlo2012]

Micron​

Thank you so much for your code, beside you made me understand about how really the counting must be, and you are completely right
also, you have a way better knowledge of programming than me.
Please let me show you now with your input what kind of results I am really expecting.

mr excel question.xlsm
	A	B	C	D	E	F
1	SCALE	PERS		BIN	LOCATION
2	1	2		1	3
3	2	5		2	1
4	3	1		3	5
5	4	5		4	out
6	5	3		5	2
7	6	10		6	9
8	7	10		7	19
9	8	3		8	27
10	9	6		9	38
11	10	15		10	6
12	11	1		11	12
13	12	11		12	36
14	13	10		13	37
15	14	11		14	out
16	15	11		15	10
17	16	7		16	OUT
18	17	2		17	34
19	18	2		18
20	19	7		19
21	20	23		20
22	21	7		21
23	22	2		22
24	23	1		23
25	24	3		24
26	25	1		25
27	26	2		26
28	27	8		27
29	28	9		28
30	29	11		29
31	30	10		30
32	31	20		31
33	32	8		32
34	33	8		33
35	34	17		34
36	35	5		35
37	36	12		36
38	37	13
39	38	9
40	39	5
41	40	13
Sheet1

Please help me here, I got this code really fishing on the internet.
Thank you Micron.

 

[Micron]

It seems like the goal posts have moved. If that continues to happen I can't help you. I thought I understood at one point but have to admit I see no logic to what you posted.

 

[montecarlo2012]

Sorry, my interpretation looks like that. As soon as I understood your statement about //

You said the right answer for 2 would be 0 but 2 is 1 line removed from line 1?

At this point when looks like a move to the goal, I just adjusted to this idea that is better than my idea in the first place.

So is this supposed to put a value from A in each row where found?

Here is when I thought you are again right, so from here put the Bin of the numbers that needed to be searched for the location
Really looks like the goal and question change but, I am not sure if that affirmation is entirely true
I am a boat navigating with your wind.
Anyway if you decide the opposite I really understand your position.
I really appreciate your kindness, thank you very much for your time, and sorry if I bother you.

 

[Micron]

So for the Bins 18, 19, 21, 22 Location value is "out" and for Bin 23 Location value is 20?

What are the Location values for Bin 24 to 36, because your Pers values don't go beyond 23?

And why does Bin 2 = Location 1 when there are at least 5 Pers rows that are 2? Only the first instance of Pers 2 is considered?

If none of that makes sense then I have no idea what you want now. Even if it does make sense I'm not sure if I can help.

 

[montecarlo2012]

Sorry again, Micron.

not sure if I can help.

I understand.
Just in case, a curious thing happens and some help happens I am adding some explanation, thanks.
PERS. is an array with 9000 rows, I pick 40 for illustration purposes, the reason why for this post I rather use the term "out", assuming when I use the code with the real data then all the rows will be filling out

24 to 36, because your Pers values don't go beyond 23?

the same reason, this is partial info.
The original code finds the distance among the same value in the 9k rows, but here and now the only thing I need is to count from the beginning of the array until a number is found for the
first time only, and only for the first time, no more than that, one time. That's the answer to this question

why does Bin 2 = Location 1 when there are at least 5 Pers rows that are 2?

Thanks. I really appreciate your concern.

 

[bebo021999]

Do not know actually VBA is needed or not, but lets start with formula first:

Book1
	A	B	C	D	E	F	G
1	SCALE	PERS		BIN	LOCATION		Your expectation
2	1	2		1	3		3
3	2	5		2	1		1
4	3	1		3	5		5
5	4	5		4	out		out
6	5	3		5	2		2
7	6	10		6	9		9
8	7	10		7	16		19
9	8	3		8	27		27
10	9	6		9	28		38
11	10	15		10	6		6
12	11	1		11	12		12
13	12	11		12	36		36
14	13	10		13	37		37
15	14	11		14	out		out
16	15	11		15	10		10
17	16	7		16	out		OUT
18	17	2		17	34		34
19	18	2		18	out
20	19	7		19	out
21	20	23		20	31
22	21	7		21	out
23	22	2		22	out
24	23	1		23	20
25	24	3		24	out
26	25	1		25	out
27	26	2		26	out
28	27	8		27	out
29	28	9		28	out
30	29	11		29	out
31	30	10		30	out
32	31	20		31	out
33	32	8		32	out
34	33	8		33	out
35	34	17		34	out
36	35	5		35	out
37	36	12		36	out
38	37	13
39	38	9
40	39	5
41	40	13
Sheet1

Cell Formulas
Range	Formula
E2:E37	E2	=IFERROR(MATCH(D2,$B$2:$B$41,0),"out")