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:
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:
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?
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:
Code:
myText = "Widget Name"
MsgBox ActiveSheet.Evaluate(" MAX((O2:O200=" & """ & myText & """ & ")*(BX2:BX200)) ")
Code:
maxRow = ActiveSheet.Evaluate("COUNTA(D:D)")
ActiveSheet.Evaluate (" COUNTIF(O2:O" & maxRow & ", """ & myText & """ ) ")