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

Question

I know that this question is similar to others, but my problem is with a particular aspect of a common solution. This is my test data (ignore the Name column). This worksheet is called "In_Out", and the ranges, starting from row 2, are named "In_Out_QR_Codes" (there's another column of QR codes on a different sheet), "In_Time", and "Out_Time".

I need to get the row number of the last row, ie highest row number, for which

• the qr code matches that in E14,
• the in_time is not blank,
• the out_time is blank.

I have most of the solution.

In excel I can get this row number with this function:

=MATCH(1,(E14 = In_Out_QR_Codes)*(NOT(ISBLANK(In_Time)))*(ISBLANK(Out_Time)),0)

I need this in vba, since I have a button on another sheet which populates the Out_Time cell of that found row with the current time. The problem is that I can't translate E14 = In_Out_QR_Codes properly.

This is what I think should work:

With Worksheets("In_Out")
   Dim matchingQrCode As Variant
      matchingQrCode = (currentQrCode = Range("In_Out_QR_Codes").Value) * 1

   Dim rowNumber as Integer
      rowNumber = Application.Match(1, matchingQrCode * outTimeBlank * inTimeNotBlank, 0)
End With

I've omitted the definitions for outTimeBlank and inTimeNotBlank because they are trivial and working. I've tried a few things for defining matchingQrCode (without .Value, without *1, and more), but I always get a type mismatch run time error.

How can I properly translate E14 = In_Out_QR_Codes from an excel function to VBA? Or is there a better overall solution to what I'm trying?

Thank you

Answer 1

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.