VBA MaxIfs problem with criteria date < cell value

[Invisibleman]

Dear all,

In VBA I am trying to get the max date value if the date value is lower than the date in another cell. However the result, is skipping a whole month it seems.

As you may see in the source, the last date is March 02, 2025, then the date before is February 27, 2025, which I therefore also want to get in the cell E6, however I am getting the January 27, 2025 as the result.

The Code I am using is;

Private Sub SetDailyPeriod()
Dim test As Variant
''Call SetNameRange("D4", "E14", "VBA_DailyPeriod")

With New_RAW_PurchaseDetails
    Range("E4") = Application.WorksheetFunction.MinIfs(.Range("E:E"), .Range("F:F"), "Buy") 'Result is okay = 24-2-2024
    Range("E5") = Application.WorksheetFunction.MaxIfs(.Range("E:E"), .Range("F:F"), "Buy") 'Result is okay = 2-3-2025
    Range("E6") = Application.WorksheetFunction.MaxIfs(.Range("E:E"), .Range("F:F"), "Buy", .Range("E:E"), "<" & Range("E5")) 'Result is wrong = 27-1-2025, where it should be 27-2-2025
End With

End Sub

The source of the selected ranges is;

Which results in;

If anybody has an idea and can help me that would be very appriciated.

Regards,
Hans

 

[Alex Blakenburg]

Add CLng to convert your date in calc for E6.

    Range("E6") = Application.WorksheetFunction.MaxIfs(.Range("E:E"), .Range("F:F"), "Buy", .Range("E:E"), "<" & CLng(Range("E5")) )

 

[Invisibleman]

Add CLng to convert your date in calc for E6.

    Range("E6") = Application.WorksheetFunction.MaxIfs(.Range("E:E"), .Range("F:F"), "Buy", .Range("E:E"), "<" & CLng(Range("E5")) )

Hello Alex,

Cool thanks for the quick response and good solution. This indeed does the job.

Regards,
Hans

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.