Macro VBA to find row from two different column

[kayza]

Hello everyone,
I need help finding the location of rows from 2 different criteria that are in 2 different columns. and here is an example of the data that I have which is located in sheet1
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Data-1[/TD]
[TD]Data-2[/TD]
[TD][/TD]
[TD]FIND :[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ec[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]ec[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ec[/TD]
[TD]z1[/TD]
[TD][/TD]
[TD]v7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]dh[/TD]
[TD]r[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ec[/TD]
[TD]v7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]dh[/TD]
[TD]kr[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]sp[/TD]
[TD]ro[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Example of the results I want to show are as follows :

If the value in cell D2 = ec and cell D3 = v7
then the message box will display the row location (5)



Any help is appreciated. Thanks!

Kayza

 

[steve the fish]

This should work:

'array parameters
fr = 2 'first row with data
lr = Range("A" & Rows.Count).End(xlUp).Row 'calculate last row
fc = 1 'first column of data

'search parameters
search1 = Range("D2").Value 'first searched for value
search2 = Range("D3").Value 'second searched for value

'create array and complete searches
arr = Range(Cells(fr, fc), Cells(lr, fc + 1))
For i = LBound(arr) To UBound(arr)
    If arr(i, 1) = search1 Then
        If arr(i, 2) = search2 Then
            MsgBox "Found in row " & i + fr - 1
        End If
    End If
Next

 

[kayza]

Thanks for your Quick response
its work like a charm

thanks

 

[My Aswer Is This]

Try this:

Sub Find_Me()
'Modified 8/2/2019 4:59:33 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
ans = "No"
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
If Cells(i, 1).Value = Range("D2").Value And Cells(i, 2).Value = Range("D3").Value Then ans = "Yes": MsgBox "Found on Row  " & i: Exit Sub
Next
If ans = "No" Then MsgBox "Not found"
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

I note that you have asked for a macro but in case that is not vital for you this formula suggestion may be of use.

Excel Workbook
	A	B	C	D
1	Data-1	Data-2		FIND :
2	ec	x		ec
3	ec	z1		v7
4	dh	r		5
5	ec	v7
6	dh	kr
7	sp	ro
8
Row Found

If you really do need/want a macro then you could try

Sub Find_Row()
  MsgBox Evaluate("AGGREGATE(15,6,ROW(A1:A2000)/((A1:A2000=D2)*(B1:B2000=D3)),1)")
End Sub