FormulaArray - Works in worksheet, not via Macros ?
 

I have a formulaarray that I need implemented via a macro. I doing
conditional summing and division.
My macro code is:
-- ActiveCell.FormulaArray =
"=SUM(IF($F$2:$F$1720,(""SHOE""=$B$2:$B$172)*(""A RM""=$D$2:$D$172)*(""LEG""=$L$2:$L$172)*(""TIME""= $M$2:$M$172)*($N$2:$N$172)/($F$2:$F$172)),0)" <--

This leaves a #Value in the ActiveCell

The completed equation is:
--
=SUM(IF($F$2:$F$1720,("SHOE"=$B$2:$B$172)*("ARM"= $D$2:$D$172)*("LEG"=$L$2:$L$172)*("TIME"=$M$2:$M$1 72)*($N$2:$N$172)/($F$2:$F$172)),0) <--

When I pasted the completed equation into the ActiveCell and terminate with
Alt-Ctrl-Enter, the FormulaArray does the calculation correctly.

Any clue why the macro assignment doesn't work?

Thanks,
- Pat

FormulaArray - Works in worksheet, not via Macros ?
 

Works for me in XL 2003 - there was a bug in Excel 95 - the workaround was
to use

ActiveCell.Offset(0,0).

instead of just

ActiveCell.

HTH,
Bernie
MS Excel MVP


"Dreiding" wrote in message
...
I have a formulaarray that I need implemented via a macro. I doing
conditional summing and division.
My macro code is:
-- ActiveCell.FormulaArray =
"=SUM(IF($F$2:$F$1720,(""SHOE""=$B$2:$B$172)*(""A RM""=$D$2:$D$172)*(""LEG""=$L$2:$L$172)*(""TIME""= $M$2:$M$172)*($N$2:$N$172)/($F$2:$F$172)),0)"
<--

This leaves a #Value in the ActiveCell

The completed equation is:
--
=SUM(IF($F$2:$F$1720,("SHOE"=$B$2:$B$172)*("ARM"= $D$2:$D$172)*("LEG"=$L$2:$L$172)*("TIME"=$M$2:$M$1 72)*($N$2:$N$172)/($F$2:$F$172)),0)
<--

When I pasted the completed equation into the ActiveCell and terminate
with
Alt-Ctrl-Enter, the FormulaArray does the calculation correctly.

Any clue why the macro assignment doesn't work?

Thanks,
- Pat

FormulaArray - Works in worksheet, not via Macros ?
 

Turns out that the system rectified itself the next day.
Haven't had a problem since. Could be related to my company network and
secuity?. Don't know, don't care.

"Bernie Deitrick" wrote:

Works for me in XL 2003 - there was a bug in Excel 95 - the workaround was
to use

ActiveCell.Offset(0,0).

instead of just

ActiveCell.

HTH,
Bernie
MS Excel MVP


"Dreiding" wrote in message
...
I have a formulaarray that I need implemented via a macro. I doing
conditional summing and division.
My macro code is:
-- ActiveCell.FormulaArray =
"=SUM(IF($F$2:$F$1720,(""SHOE""=$B$2:$B$172)*(""A RM""=$D$2:$D$172)*(""LEG""=$L$2:$L$172)*(""TIME""= $M$2:$M$172)*($N$2:$N$172)/($F$2:$F$172)),0)"
<--

This leaves a #Value in the ActiveCell

The completed equation is:
--
=SUM(IF($F$2:$F$1720,("SHOE"=$B$2:$B$172)*("ARM"= $D$2:$D$172)*("LEG"=$L$2:$L$172)*("TIME"=$M$2:$M$1 72)*($N$2:$N$172)/($F$2:$F$172)),0)
<--

When I pasted the completed equation into the ActiveCell and terminate
with
Alt-Ctrl-Enter, the FormulaArray does the calculation correctly.

Any clue why the macro assignment doesn't work?

Thanks,
- Pat