0

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

See the following picture of the data

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?

Sjamsing
  • 57
  • 4
  • Why don't you put a formula on the excel-sheet - next to the orderIDs of the three items? – Ike Nov 11 '21 at 15:13
  • 1
    Is D2 definitely formatted as a date? Try `val(dtClosingTime)` in your formula. – SJR Nov 11 '21 at 15:21
  • 1: @SJR I thought that this was the solution, but I also checked for amounts less than the dtClosingTime, and I could not get it to work. – Sjamsing Nov 12 '21 at 13:47
  • 2: @SJR I found that when using the val(dtClosingTime), it gives me “23” and, therefore, all time stamps are greater than this value. To answer your question re formatting, I have tested that (23/10/2021 15:35:17 > dtClosingTime ; 23/10/2021 15:35:17 < dtClosingTime ; 23/10/2021 15:08:14 > dtClosingTime ; 23/10/2021 13:35:24 < dtClosingTime), where dtClosingTime = 23/10/2021 15:29:38 with the result (TRUE ; FALSE ; FALSE ; TRUE) as expected, hence I believe the formatting is fine. I’m still puzzled why this doesn’t work. – Sjamsing Nov 12 '21 at 13:47
  • @lke Both my macro and workbook are more complex than the simplified version I posted here. I use different external sources which updates a lot of data all the time, while it also changes ranges, dtClosingTime, and more. My workbook is pretty heavier and, therefore, I am trying to minimize the use of formulas in Excel directly. Otherwise, I agree with you that it would be much simpler. In fact, I did this to test what formula gave me the result I wanted and then took it to VBA, but I can’t get it to work there. – Sjamsing Nov 12 '21 at 13:47

0 Answers0