Array Formula Question
 

I have a column of formulas that I'd like to change under macro control.
One would be an array formula, and the other a simple SUMIF. I'd prefer not
to use a pivot table.

The more complicated case is finding what ranges dates fall within, and
summing the values associated with those dates. This was my try at it. The
macro runs and puts in the formula, but it doesn't work:

Range("D14").FormulaArray = "=SUM(IF(AND(Dates!B13<=LossYearColumn,LossYearCol umn<=Dates!C13),ExcludedColumn))"

This would then then have a variant going down for another 12 cells.
Preferrably done without a loop.

For the simple SUMIF case this works fine:

Range("D14:D26").FormulaR1C1 = "=IF(R[0]C[-3]="""",0,SUMIF(LossYearColumn,R[0]C[-3],ExcludedColumn))"

Thanks, Don <donwiss at panix.com.

Array Formula Question
 

On Wed, 17 Dec 2003 19:18:22 -0500, Don Wiss wrote:

I have a column of formulas that I'd like to change under macro control.
One would be an array formula, and the other a simple SUMIF. I'd prefer not
to use a pivot table.

The more complicated case is finding what ranges dates fall within, and
summing the values associated with those dates. This was my try at it. The
macro runs and puts in the formula, but it doesn't work:

Range("D14").FormulaArray = "=SUM(IF(AND(Dates!B13<=LossYearColumn,LossYearCol umn<=Dates!C13),ExcludedColumn))"

This would then then have a variant going down for another 12 cells.
Preferrably done without a loop.

Well, no one responded. So I worked up a solution. Not overly elegant, but
it works. It is:

here = ActiveCell.Address
Range("D14").FormulaArray = "=SUM(IF(LossYearColumn<=Dates!C13,ExcludedColumn) )-SUM(IF(LossYearColumn<=Dates!B13,ExcludedColumn))"
Range("D14").Copy
Range("D15:D26").PasteSpecial Paste:=xlFormulas
Range(here).Select

Don <donwiss at panix.com.