Ctrl+Shift+Enter in VBA

[Roderick_E]

Ok this sounds weird but I need to know if there is a way to do a ctrl+shift+enter (array) in VBA. I often use application.match in my VBA and want to use it for multiple criteria so I can get the row number match. How do I do this? Thanks

Multiple criteria match in basic formula:

=match(1,(string1=range1)*(string2=range2),0)

This fails in VBA


Edit: P.S. I also tried application.match(string1 & string2, range1 & range 2,0)

 

[RedBeard]

This might be of use: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-formulaarray-property-excel

 

[Roderick_E]

Thanks but that is inserting the formula into a worksheet. I just want the result. This looked promising but still doesn't work:

https://stackoverflow.com/questions/13748780/create-cse-array-formula-in-excel-macro

Application.Evaluate("Match(" & string1 & "& string2, range1 & range2, 0)")

Also failed

 

[JoeMo]

Application.Evaluate can be used to calculate an array formula. See: https://fastexcel.wordpress.com/201...to-make-excels-evaluate-method-twice-as-fast/

 

[Roderick_E]

Application.Evaluate can be used to calculate an array formula. See: https://fastexcel.wordpress.com/201...to-make-excels-evaluate-method-twice-as-fast/

Yeah thanks, I think I just need to figure out the correct formatting of the string to evaluate. I'll report back.

 

[Roderick_E]

Ok finally. I had to take the static elements of the string to evaluate and combine them with the dynamic then evaluate

evalstr = "static stuff wrapped in quotes" & dynamic & "more static"
sheet#.evaluate(evalstr)

Works great! Thanks for all the help. I'll be using this method for year