find row No

[Excel777]

i want to find row no of -1
results is column D

Excel Workbook
	A	B	C	D
1	Data		Number	Row
2	-1		-1	2
3	1		-1	6
4	-6		-1	8
5	5		-1	12
6	-1		-1	14
7	1
8	-1
9	4
10	-2
11	1
12	-1
13	1
14	-1
Sheet2

 

[chuckles1066]

Sub chuckles1066()

Dim lr As Long
Dim a As Long
Dim b As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
b = 2
For a = 2 To lr
Select Case Cells(a, 1).Value
    Case -1
        Cells(b, 3).Value = Cells(a, 1).Value
        Cells(b, 4).Value = a
        b = b + 1
End Select
Next
End Sub

 

[Excel777]

thanks but i don't need a macro i need a formula

 

[Excel777]

need a formula to drag down

 

[T. Valko]

need a formula to drag down

Try this...

Book1
	A	B	C	D
1	Data	_	Number	Row
2	-1	_	-1	2
3	1	_	Count	6
4	-6	_	5	8
5	5	_	_	12
6	-1	_	_	14
7	1	_	_
8	-1	_	_
9	4	_	_
10	-2	_	_
11	1	_	_
12	-1	_	_
13	1	_	_
14	-1	_	_
Sheet1

Enter this formula in C4 to get the count of records that meet the criteria:

=COUNTIF(A:A,C2)

Enter this array formula** in D2 to return the row numbers:

=IF(ROWS(D$2:D2)>C$4,"",SMALL(IF(A$2:A$14=C$2,ROW(A$2:A$14)),ROWS(D$2:D2)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

 

[Excel777]

Many Thanks T. Valko Works Fine....

 

[T. Valko]

Many Thanks T. Valko Works Fine....

You're welcome. Thanks for the feedback!