Get Minimum Date value VBA

Question

I am trying to get the minimum value date if the column next to it has the word "before". I tried the following formula, but I am getting a value of 0. Am I missing something here?

Sub test()
Dim i As Long
Dim j As Long
Dim LRow As Long
Dim b As Long
Dim a As String
    a = "before"
    j = 0
LRow = Range("A" & Rows.Count).End(xlUp).Row

Dim MyArr() As Variant
ReDim MyArr(1 To LRow) As Variant
  
For i = 1 To LRow
 If VBA.InStr(Range("B" & i).Value, a) > 0 Then
        j = j + 1
        MyArr(j) = Range("A" & i).Value
  End If
Next i
   
    ReDim Preserve MyArr(1 To j)
    
Range("D1").Value = Application.WorksheetFunction.Min(MyArr())

End Sub

Answer 1

Use MINIFS:

=MINIFS(A:A, B:B, "*before*")

With VBA:

Sub test()
    Range("D1").Value = WorksheetFunction.MinIfs(Range("A:A"), Range("B:B"), "*before*")
    Range("D1").NumberFormat = "m/d/yyyy"
End Sub