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

 

[Tetra201]

Try

=LOOKUP(2,1/((C6:C52&" | "&C7:C53)=C1),ROW(C7:C53))

 

[jim may]

A VBA solution... In A Standard Module... (Prior to assigning the colorindex 4 inside the code from the immediate window I had to

type:
? Cells(48,3).Interior.colorindex

Where I got the numeric 4 You ought to do the same thing and make sure the two number agree...

Sub Foo()
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 3 To 5
    For j = LR To 4 Step -1
        If Cells(j, i).Interior.ColorIndex = 4 Then
            Cells(3, i).Value = j
            Exit For
        End If
    Next j
Next i
End Sub

 

[motilulla]

Try

=LOOKUP(2,1/((C6:C52&" | "&C7:C53)=C1),ROW(C7:C53))

Tetra201, it worked as appeal! Thank you so much for your kind help!

Kind Regards,
Moti

 

[Tetra201]

Glad to have helped.

 

[motilulla]

A VBA solution... In A Standard Module... (Prior to assigning the colorindex 4 inside the code from the immediate window I had to

type:
? Cells(48,3).Interior.colorindex

Where I got the numeric 4 You ought to do the same thing and make sure the two number agree...

Sub Foo()
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 3 To 5
    For j = LR To 4 Step -1
        If Cells(j, i).Interior.ColorIndex = 4 Then
            Cells(3, i).Value = j
            Exit For
        End If
    Next j
Next i
End Sub

jim may</SPAN>

(Prior to assigning the colorindex 4 inside the code from the immediate window I had to</SPAN></SPAN>

type:</SPAN></SPAN>
? Cells(48,3).Interior.colorindex</SPAN></SPAN>

Where I got the numeric 4 You ought to do the same thing and make sure the two number agree...</SPAN></SPAN>

This was a very new situation for me to understand what is a immediate window after hours searching I find how do you get the "Cells(48,3).Interior.colorindex" </SPAN>number 4 </SPAN></SPAN>for that I required VBA as below</SPAN></SPAN></SPAN>

</SPAN></SPAN>[COLOR=#000000]
Sub find_Colour()[/COLOR]</SPAN></SPAN>[COLOR=#000000]
Debug.Print Cells(48, 3).Interior.ColorIndex[/COLOR]</SPAN></SPAN>[COLOR=#000000]
End Sub[/COLOR]</SPAN>[COLOR=#000000]
[/COLOR]

</SPAN>
I like the code idea is innovative but jim in the post#1 I filled colour to make the example clearer my question is VBA could be made with the colour usage </SPAN></SPAN>

Thank for your help</SPAN></SPAN>

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

 

[motilulla]

jim may</SPAN>
VBA could be made with the colour usage
</SPAN></SPAN>Moti</SPAN></SPAN>

Sorry jim, there was a typo error VBA could be made without the colour usage

 

[jim may]

Sorry jim, there was a typo error VBA could be made without the colour usage

Re: Find row number

Originally Posted by motilulla

Sorry for my miscommunication but hopefully something was learned from the experience..
I went back to the Macro and Altered/Modified it so as to accomplish your desired GOAL.
Here it is, below. Test it out on a BACKUP Copy of your file.

Sub Foo()
Dim arr As Variant
Dim LR As Long
Dim i As Integer, j As Integer
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 3 To 5
sLookFor = WorksheetFunction.Substitute(Cells(1, i), " ", "")
arr = Split(sLookFor, "|")
    For j = LR To 4 Step -1
        If CStr(Cells(j - 1, i).Value) = arr(0) And CStr(Cells(j, i).Value) = arr(1) Then
            Cells(3, i).Value = j
            Exit For
        End If
    Next j
Next i
End Sub

<strike></strike>




​

​

<strike></strike>

 

[motilulla]

Re: Find row number

Originally Posted by motilulla

Sorry for my miscommunication but hopefully something was learned from the experience..
I went back to the Macro and Altered/Modified it so as to accomplish your desired GOAL.
Here it is, below. Test it out on a BACKUP Copy of your file.

Sub Foo()
Dim arr As Variant
Dim LR As Long
Dim i As Integer, j As Integer
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 3 To 5
sLookFor = WorksheetFunction.Substitute(Cells(1, i), " ", "")
arr = Split(sLookFor, "|")
    For j = LR To 4 Step -1
        If CStr(Cells(j - 1, i).Value) = arr(0) And CStr(Cells(j, i).Value) = arr(1) Then
            Cells(3, i).Value = j
            Exit For
        End If
    Next j
Next i
End Sub

<STRIKE></STRIKE>




​

​

<STRIKE></STRIKE>

jim may, thank you so much for altering the VBA, it is working as appeal </SPAN></SPAN>

I appreciate your kind help. Have a good day</SPAN></SPAN>

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

 

[motilulla]

Hello, Please would it be possible that VBA could worked without the conditions declared in C1:E1 as shown in the opening post (what I mean that the those conditions can be set internally in the VBA?)</SPAN></SPAN>

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