title: Create CSE Array Formula in Excel Macro
body: <p>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</p>
  
  <p>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</p>
  
  <p>within excel itself, the formula below works perfectly:
  <code>{=Match(1,(A:A=1)*(B:B=2),0)}</code></p>
  
  <p>However, I would now like to use it within my macro along the lines of:
  <code>variable = {=Match(1,(Sheets("Right Sheet").range("A:A")=value1)*(Sheets("Right Sheet").range("B:B")=value2),0)}</code></p>
  
  <p>Anyone able to provide any light on this?
  I have played with all sorts of variants of <code>ArrayFormula</code> and had no success at all!
  Ideally I would like my <code>value1</code> and <code>value2</code> numbers to be equal to variables (1 and 2) defined previously in my macro</p>
  
  <p>Thanks in advance</p>