How can I find the highest row according to multiple criteria?-CodePudding

I know that this question is similar to others, but my problem is with a particular aspect of a common solution.

I recreated your data and notice how I've defined ranges (red square).

Code in VBA:

Sub test()

Debug.Print Evaluate("MATCH(1,(E14 = In_Out_QR_Codes)*(NOT(ISBLANK(In_Time)))*(ISBLANK(Out_Time)),0)")

End Sub

Output:

It works perfectly. I would add 1 because my defined names are referenced to row 2 so the real row number is your formula 1 (13 in this case).

But your formula is not good. If you put 1a the result is wrong:

So I've done it using SUMPRODUCT:

=SUMPRODUCT(MAX(--(In_Out_QR_Codes=E14)*--(In_Time<>"")*--(Out_Time="")*ROW(In_Out_QR_Codes)))

With VBA:

Sub test()

Debug.Print Evaluate("SUMPRODUCT(MAX(--(In_Out_QR_Codes=E14)*--(In_Time<>"""")*--(Out_Time="""")*ROW(In_Out_QR_Codes)))")

End Sub

Notice this formula will return the correct row number, no need to do 1. Also, if there is no match, it will return 0, no need to worry about trapping errors.

If you want an VBA code more classic, you can take everything into array and loop to get max row number (if there is a match):

Sub test2()
Dim MyCriteria As String
Dim MyData As Variant
Dim i As Long
Dim MaxRow As Long

MaxRow = 0
MyCriteria = Range("E14").Value
MyData = Range("B2").CurrentRegion.Value 'array of data INCLUDING headers at row 1

'array of data: first column is QR, second column is In time, third is Out_time

For i = 2 To UBound(MyData) Step 1 'start at 2 because 1 holds headers
    If MyData(i, 1) = MyCriteria Then 'check if QR code match
        If MyData(i, 2) <> "" Then 'check if in-time is not blank
            If MyData(i, 3) = "" Then MaxRow = i 'if out-time is blank, save row number)
        End If
    End If
Next i

If MaxRow = 0 Then
    Debug.Print "No match"
Else
    Debug.Print MaxRow
End If

Erase MyData

End Sub

And sorthened it owuld be:

Sub test3()
Dim MyCriteria As String
Dim MyData As Variant
Dim i As Long
Dim MaxRow As Long

MaxRow = 0
MyCriteria = Range("E14").Value
MyData = Range("B2").CurrentRegion.Value 'array of data INCLUDING headers at row 1

'array of data: first column is QR, second column is In time, third is Out_time

For i = 2 To UBound(MyData) Step 1 'start at 2 because 1 holds headers
    If MyData(i, 1) = MyCriteria And MyData(i, 2) <> "" And MyData(i, 3) = "" Then MaxRow = i
Next i

If MaxRow = 0 Then
    Debug.Print "No match"
Else
    Debug.Print MaxRow
End If

Erase MyData

End Sub

Please, notice my datasample is ignoring name column, so maybe you need to change the index column number when calling the array in your case if you use CurrentRegion. So where I typed Mydata(i,1) would be something like Mydata(i,2) and so on, because you got another colum.