Adding array brackets {} to all cells in a column??

[fern]

Hi,

I've got a very complicated spreadsheet which includes columns of arra
formulae. I had all the formulae entered correctly and it was workin
great until I changed something in the spreadsheet (?? it doesn'
really matter what happened) which reset all my array formulae back t
regular formatting - i.e. the {} were removed and the calculatio
results changed.

Now, I know that I could usually highlight each column or row and hi
CTRL+SHIFT+ENTER to switch everything back to being {} bracketed
However, in my spreadsheet each cell contains a DIFFERENT array an
there are lots of blank cells scattered throughout that would have t
be considered in my formulae too (which I don't want to do).

So does anyone know any tricks to resetting ALL the formulae (in
specific column or row) to be read as INDIVIDUAL array formulae? I'
rather not have to go through 200 cells, pressing CTRL+SHIFT+ENTER fo
each one so that my calculations come out correctly...

Oh, and just in case someone's going to ask me, I'm using Excel 200
and this is an example of just one of my array formulae:
{=(SUM(AA122:AA127*AC122:AC127))/(SUM(AC122:AC127))}

Please help - I'm going crazy here.
Thx,

 

[Nick Hodge]

Fern

Don't know if this helps but it will convert all formulae under a selection
to array formulas.

Sub convertToArray()
Dim mycell As Range
For Each mycell In Selection
mycell.FormulaArray = mycell.FormulaR1C1
Next mycell
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)