Find row number

[motilulla]

Hello,

I need a row numbers of the last pairs are highlighted in the green in the range C6:E53. Formula or VBA
For examples...
Last pair 25 | 9 find in cells C47:C48 result row number i want in cell C3 = 48
Last pair 25 | H find in cells C32:C33 result row number i want in cell C3 = 33
Last pair 25 | 25 find in cells C25:C26 result row number i want in cell C3 = 26

Book1
	A	B	C	D	E
1			25 | 9	25 | H	25 | 25
2
3		Row Nun	48	33	26
4
5			n1	n2	n3
6			H	H	H
7			H	H	H
8			H	H	H
9			25	25	25
10			9	9	9
11			H	H	H
12			H	H	H
13			9	9	9
14			9	9	9
15			9	9	9
16			H	H	H
17			9	9	9
18			9	9	9
19			9	9	9
20			25	25	25
21			9	9	9
22			25	25	25
23			9	9	9
24			25	25	25
25			25	25	25
26			25	25	25
27			9	9	9
28			25	25	25
29			H	H	H
30			25	25	25
31			9	9	9
32			25	25	25
33			H	H	H
34			9	9	9
35			H	H	H
36			9	9	9
37			9	9	9
38			25	25	25
39			9	9	9
40			9	9	9
41			9	9	9
42			25	25	25
43			9	9	9
44			H	H	H
45			25	25	25
46			9	9	9
47			25	25	25
48			9	9	9
49			9	9	9
50			9	9	9
51			H	H	H
52			9	9	9
53			25	25	25
Sheet1

Thank you all

Excel 2000
Regards,
Moti

 

[jim may]

Check out the InputBox() Statement...

 

[motilulla]

Check out the InputBox() Statement...

please can you guide me which is that line ?

 

[Tetra201]

@ motilulla:

See if the following UDF (User-Defined Function) works for you:

Function FindPairRow(S1 As String, S2 As String, Rng As Range) As Long
    FindPairRow = Evaluate("LOOKUP(2,1/((" & Rng.Resize(Rng.Rows.Count - 1).Address & "&"" | ""&" & Rng.Offset(1).Resize(Rng.Rows.Count - 1).Address & ")=""" & S1 & " | " & S2 & """),ROW(" & Rng.Offset(1).Resize(Rng.Rows.Count - 1).Address & "))")
End Function

Usage example: =FindPairRow(25,"H",C6:C53)

 

[motilulla]

@ motilulla:

See if the following UDF (User-Defined Function) works for you:

Function FindPairRow(S1 As String, S2 As String, Rng As Range) As Long
    FindPairRow = Evaluate("LOOKUP(2,1/((" & Rng.Resize(Rng.Rows.Count - 1).Address & "&"" | ""&" & Rng.Offset(1).Resize(Rng.Rows.Count - 1).Address & ")=""" & S1 & " | " & S2 & """),ROW(" & Rng.Offset(1).Resize(Rng.Rows.Count - 1).Address & "))")
End Function

Usage example: =FindPairRow(25,"H",C6:C53)

Tetra201, thank you for giving a 2nd option, your formula works fine also why I want a VBA because once you run you get the results values only, it is preferable if possible </SPAN></SPAN>

I appreciate a lot your kind help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[motilulla]

Check out the InputBox() Statement...

Check out the InputBox() Statement...