EVALUATE MAX IF CSE array function

[Vancity]

I've newly discovered the useful EVALUATE function, but am having troubles with the syntax for a MAX IF array function b/c of all the quotation marks.
I'm trying find the max date in a given data range (BX2:BX200) where the value in column O equals myText. I'll then use this max date and replace the date in BX for all records with the given widget name.

This code wrongly returns 0:

    myText = "Widget Name"
    MsgBox ActiveSheet.Evaluate(" MAX((O2:O200=" & """ & myText & """ & ")*(BX2:BX200)) ")

The code below works, I show it as an example. It finds the # of occurrences of Widget Name (in my dataset the # is typically less than 5). It uses maxRow, which I've purposely omitted from above for simplicity's sake while debugging:

maxRow = ActiveSheet.Evaluate("COUNTA(D:D)")
    
    ActiveSheet.Evaluate (" COUNTIF(O2:O" & maxRow & ", """ & myText & """ ) ")

I'm pretty sure I just have some quotes mixed up. It is a CSE array formula, but reading on another post, it doesn't require the { } brackets when using EVALUATE. Or maybe there is a better way to do this altogether?

 

[Richard Schollar]

Hi

Try:

   MsgBox ActiveSheet.Evaluate(" MAX(IF(O2:O200=""" & myText & """,BX2:BX200))")

 

[Vancity]

Perfect, thank you!!

Can you explain how the """ works in this example?

 

[Vancity]

I'm struggling with replacing the "200" with the maxRow variable I had in the working COUNTIF example. Help!

 

[Richard Schollar]

That would be:

MsgBox ActiveSheet.Evaluate(" MAX(IF(O2:O" & MaxRow &"=""" & myText & """,BX2:BX" & MaxRow & "))")

When you see two double quotes together in a string it just means 'read the presence of a single quote within the string'. So "Richard ""is"" an accountant" would translate to:

Richard "is" an accountant

So, when you see "" together in a string, you can consider it as a single " as part of the string itself. So """ means " as part of the string and a " actually terminating the string. Does this make sense?

 

[Vancity]

Yes, thanks again.
This community is fantastic, I've Googled for Excel help which has led me to Mr Excel, and finally signed in to get this problem solved and to hopefully contribute. Thanks!