Passing Array Formula result to a variable in VBA (Leo)
 

Hi,
We know that for putting an arrya formula we use "Range.formulaArray"
property.
But what about if I need to use an array formula result in the context of my
code (like myVar =workbook.formula) and passing it to my variable, without
putting it on a cell: just like MyVar or myFunction = arrayformula(sth).
Is there any way?
please help.
--
Thans & Best regards
Leo, InfoSeeker

Passing Array Formula result to a variable in VBA (Leo)
 

myVar = Activesheet.Evaluate(the_array_formula)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Leo" wrote in message
...
Hi,
We know that for putting an arrya formula we use "Range.formulaArray"
property.
But what about if I need to use an array formula result in the context of
my
code (like myVar =workbook.formula) and passing it to my variable, without
putting it on a cell: just like MyVar or myFunction = arrayformula(sth).
Is there any way?
please help.
--
Thans & Best regards
Leo, InfoSeeker

Passing Array Formula result to a variable in VBA (Leo)
 

thank you so much for your reply, it did great help!
after a 2 or 3 times of error I managed to run this code with no error;
Public Sub MySub()
Dim myVar As Long
myVar =
Application.Evaluate("=SUM(IF((Sheet1!$A$2:$A$2000 =""P07"")*(Sheet1!$H$2:$H$2000=200707),Sheet1!$U$2 :$U$2000,0))")
Debug.Print myVar
End Sub

the point was that it should be used withouth the symbol "{}"
--
Thans & Best regards
Leo, InfoSeeker


"Bob Phillips" wrote:

myVar = Activesheet.Evaluate(the_array_formula)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Leo" wrote in message
...
Hi,
We know that for putting an arrya formula we use "Range.formulaArray"
property.
But what about if I need to use an array formula result in the context of
my
code (like myVar =workbook.formula) and passing it to my variable, without
putting it on a cell: just like MyVar or myFunction = arrayformula(sth).
Is there any way?
please help.
--
Thans & Best regards
Leo, InfoSeeker