Create CSE Array Formula in Excel Macro

Question

i have very recently started to learn to create VBA macros in Excel, however, I am completely stumped as to how to create a CSE array formula within my macro

I would like to define a variable as the row in which the value in column A in a sheet = value 1 and column B = value 2

within excel itself, the formula below works perfectly: {=Match(1,(A:A=1)*(B:B=2),0)}

However, I would now like to use it within my macro along the lines of: variable = {=Match(1,(Sheets("Right Sheet").range("A:A")=value1)*(Sheets("Right Sheet").range("B:B")=value2),0)}

Anyone able to provide any light on this? I have played with all sorts of variants of ArrayFormula and had no success at all! Ideally I would like my value1 and value2 numbers to be equal to variables (1 and 2) defined previously in my macro

Thanks in advance

Tim Williams · Accepted Answer · 2012-12-06 18:49:23Z

2

This (eg) worked for me:

Debug.Print Application.Evaluate("SUM(A1:A5*B1:B5)")

you need to build up the formula into a string, and leave off the "=" and "{}".

Debug.Print Application.Evaluate("Match(1,(Sheet1!A:A=" & val1 & _
                                  ")*(Sheet1!B:B=" & val2 & "),0)")

answered Dec 6, 2012 at 18:49

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create CSE Array Formula in Excel Macro

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related