MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Array Formulas


Posted by Steve on February 14, 2002 9:13 AM

Hello,

I've got a sheet with 1,332 cells that need to be array formulas. Surely there is a better way than hitting F2, cntrl+shift+enter for all 1,332 cells??? I highlight the range, but it changes the formulas! I even tried writing a macro to step through each cell! Please help...on a deadline here:)

Steve


Posted by IML on February 14, 2002 9:48 AM

If you can, set up the formulas with abs/rel reference so you can just array enter it once and then copy and past to other cells.
Otherwise, maybe you could get away with using a sumproduct subsitite depending on the formula?

Posted by Mark W. on February 14, 2002 1:17 PM

Someone (more capable than me) could write a VBA macro...

...that traverses a cell range, "snatches" the
formula in each cell and replaces it with an
array formula equivalent. This macro should
require only a few lines of code... at least
that's all it would take in the "old" Macro 4.0
language.

Posted by Ivan F Moala on February 14, 2002 6:13 PM

Re: Someone (more capable than me) could write a VBA macro...

Just select your formulas and run this
macro.......


Sub ChangeToArrayFormula()
Dim frmCell As Range

For Each frmCell In Selection
If frmCell.HasFormula Then
frmCell.FormulaArray = frmCell.FormulaArray
End If
Next

End Sub

HTH


Ivan

Posted by Ivan F Moala on February 14, 2002 6:15 PM

Re: Someone (more capable than me) could write a VBA macro...

Just select your formulas and run this macro....

Sub ChangeToArrayFormula()
Dim frmCell As Range

For Each frmCell In Selection
If frmCell.HasFormula Then
frmCell.FormulaArray = frmCell.FormulaArray
End If
Next

End Sub


HTH


Ivan