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

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

<tbody>
</tbody>

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