Array Formula Question

  • Thread starter Thread starter Don Wiss
  • Start date Start date
D

Don Wiss

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,LossYearColumn<=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>.
 
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,LossYearColumn<=Dates!C13),ExcludedColumn))"

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

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>.
 
Back
Top