("vba & code")// counting cells

[montecarlo2012]

Hello.

Book1
	B	C
2	0	11
3	2
4	3
5	4
6	5
7	9
8	7
9	8
10	9
11	2
12	3
13	4
14	5
15	9
16	7
17	8
Sheet1

In column B2 I have this array and in C the answer.
Trying to count how many cells are until a value is found.
this is the try:

Sub Locat()
Set rngData = Range("b2:b25")
For Each cell In rngData
    If cell = 9 Then
        Range("c2").Value = n
          Else
       n = n + 1
    End If
Next
End Sub

if cell = 9 the answer expected on c2 is 6, if you count how many cells are until the ("first") 9 is founded, there are 6 places,
if cell = 2 the answer expected on c2 is 2
if cell = 0 the answer will be 1 etc.
but my snippet code give me the wrong count

Thank you for reading this, and will be nice some help.

 

[HongRu]

Try this.

Sub Locat()
    Set rngData = Range("b2:b25")
    For Each cell In rngData
        If cell = 9 Then
            n = n + 1
            Range("c2").Value = n
            Exit Sub
        Else
           n = n + 1
        End If
    Next
End Sub

 

[bebo021999]

Input number into an inputbox then hit OK:

Option Explicit
Sub Locat()
Range("c2").Value = Evaluate("=IFERROR(MATCH(" & InputBox("Which number?") & ",B2:B25, 0),""not exists!"")")
End Sub

 

[montecarlo2012]

Hello.
Thank you bebo021999 for your input.

 

[montecarlo2012]

HongRu.
Thank you. is the solution, so I marked it. my concern is the logic behind, as a code, not a compact formula