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
