warning: cannot change part of an array. how do I by pass this?

[Guest]

When trying to change the array in a workbook, I am getting the message
"Cannot change part of array". I do need to change the array, I beleive I
can by pass this but am not sure how to do it. Any suggestions?

 

[Bob Phillips]

It means that is a range array formula, so you need to select all cells in
that array before changing it. Just step through each cell until you find
the last one.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Roger Govier]

Hi Gwyneth

Select the complete range of cells with the array formula before trying to
do the edit.

Regards

Roger Govier

 

[Robert_Steel]

Gwyneth
A way to do this was shown to me in this group by Tushar Mehta and Harlan
Grove

A search for "Editing Array Formula" in this group should help find the
original

Method I now use:
Select any cell in the Array
press f2 then Ctrl+Shift+ Enter
(you now have the whole array selected)
press f2 then Ctrl+Enter
(this converts the array into individual formula)
you can now delete any cells you wish
now select the new array you want and correct the formula
comit with Ctrl+Shift+Enter

Sounds long winded but does work for me (plus it came v.respected members
of the forum so will not be far wrong)

hth RES