How to use operator in MAXIFS VBA EXCEL

Question

Hope someone can help me. My main issue is to get the “>=” part to work in the Application.WorksheetFunction.MaxIfs function in VBA.

I have a table with:

• One header row
• 125 rows of data
• Three columns: Time, Amount and Order ID

I have three items (Order IDs) in a collection, collOrderID, which I loop over later. Also, I set three ranges corresponding to the three columns in the table and assign a date and time to the variable dtClosingTime.

My objective when looping through the items in the collOrderID is to get the maximum amount if 1) the Order ID matches the current Order ID in the collection we are looking at and if 2) the Time is greater than or equal to dtClosingTime.

Please see the code below.

Sub test()
    Dim collOrderID As New Collection
    Dim item As Variant
    Dim dtClosingTime As Date
    Dim rngTimeAuction As Range
    Dim rngAmountAuction As Range
    Dim rngOrderIDAuction As Range
    Dim intAmountInAuction As Integer
    
    'Add three items to collection
    collOrderID.Add 210481095
    collOrderID.Add 211145007
    collOrderID.Add 74163756
    
    'Set ranges
    Set rngTimeAuction = ThisWorkbook.Worksheets("Sheet1").Range("B8:B132")
    Set rngAmountAuction = ThisWorkbook.Worksheets("Sheet1").Range("C8:C132")
    Set rngOrderIDAuction = ThisWorkbook.Worksheets("Sheet1").Range("D8:D132")
    
    'Assign closing time
    dtClosingTime = ThisWorkbook.Worksheets("Sheet1").Range("D2")
    
    For Each item In collOrderID
            'Compute amount: Must be orderID in collection and later than dtClosingTime
            intAmountInAuction = Application.WorksheetFunction.MaxIfs(rngAmountAuction, rngOrderIDAuction, item, rngTimeAuction, ">=" & dtClosingTime)
            
            'Show answer
            MsgBox intAmountInAuction
    Next item
End Sub

When executing this code, I would expect to get three message boxes with the three green values, i.e. 4822, 6498 and 5400, in that order, but instead I get three 0s (Please see the snapshot I have attached).

I am quite confident that it has something to do with the “>=” part of the MaxIfs function, as I have also tried matching with just the dtClosingTime and then changing one of the Time values to dtClosingTime to see that I get the right result.

Does anyone know what I am missing here?