Can an array function be executed from a macro, how?

I'd like to construct an array function like

{SUM(IF(A$1:A$500<B1,C1:C500, 0))}

in a macro and call it .. using smthg similar to Application.Worksheetfunction


TIA

The formula can be included in a macro as:

ActiveSheet.Range("D1") = "=SUM(IF(A$1:A$500<B1,C1:C500, 0))"

I don't know if this is what you were looking for.
--
Best wishes,

Jim


"Bharath Rajamani" wrote:


Can an array function be executed from a macro, how?

I'd like to construct an array function like

{SUM(IF(A$1:A$500<B1,C1:C500, 0))}

in a macro and call it .. using smthg similar to Application.Worksheetfunction


TIA

Thx Jim, but this is not what I was looking for. I want a cell .. say D1 ..
to contain the result of the array function ...

Cells.formulaR1C1 does not work

The array formula is constructed dynamically in the macro, and I'd like the
macro to execute the array formula, and store the result in a cell




"Jim Jackson" wrote:

The formula can be included in a macro as:

ActiveSheet.Range("D1") = "=SUM(IF(A$1:A$500<B1,C1:C500, 0))"

I don't know if this is what you were looking for.
--
Best wishes,

Jim


"Bharath Rajamani" wrote:


Can an array function be executed from a macro, how?

I'd like to construct an array function like

{SUM(IF(A$1:A$500<B1,C1:C500, 0))}

in a macro and call it .. using smthg similar to Application.Worksheetfunction


TIA

Heres a short script that will help:
'If you just wanted to output the actual formula to the cell and
execute then:
Sheet1.Range("D1").FormulaArray = "=SUM(IF(A$1:A$500<B1,C1:C500,
0))"

'If you actually want the code to calculate the value and then
output the value:
answer = Evaluate("=SUM(IF(A$1:A$500<B1,C1:C500, 0))")
Sheet1.Range("D1") = answer

Regards

Bharath Rajamani wrote:
Thx Jim, but this is not what I was looking for. I want a cell .. say D1 ..
to contain the result of the array function ...

Cells.formulaR1C1 does not work

The array formula is constructed dynamically in the macro, and I'd like the
macro to execute the array formula, and store the result in a cell




"Jim Jackson" wrote:

The formula can be included in a macro as:

ActiveSheet.Range("D1") = "=SUM(IF(A$1:A$500<B1,C1:C500, 0))"

I don't know if this is what you were looking for.
--
Best wishes,

Jim


"Bharath Rajamani" wrote:


Can an array function be executed from a macro, how?

I'd like to construct an array function like

{SUM(IF(A$1:A$500<B1,C1:C500, 0))}

in a macro and call it .. using smthg similar to Application.Worksheetfunction


TIA