Re Array

[Steved]

Hello from Steved

I was given the below Code for Control Shift Enter

Please can we devolope it so that when I highlite E2:J8
it will turn the cells into ARRAY as using below is
taking cell E2 and coping it to E3:J2 after I have
highlited E2:J2 and control Shift Enter. As each cell has
a different formula

Dim cell As Range

For Each cell In Selection
cell.FormulaArray = cell.Formula
Next cell
Thankyou.

 

[Juan Sanchez]

Steved

works for me, heres a little twist:

'===========================
Sub ReArray()
Dim C As Range
Set C = Selection
For Each C In C
C.FormulaArray = C.Formula
Next C
End Sub
'============================

See if it works...

Cheers
Juan

 

[Steved]

Hello Juan from Steved

It works a treat and thankyou as there is 217 of them.

Cheers

 

[Juan Sanchez]

Glad to help

Cheers
Juan

-----Original Message-----
Hello Juan from Steved

It works a treat and thankyou as there is 217 of them.

Cheers

.

 

[Stephen Dunn]

Hi Steved,

If you highlighted E2:J2 and used Ctrl+Shift+Enter, it was that action that
turned them all the same. Nothing to do with the original code, or the
superfluous changes that Juan made to the code.

Array formulae can be single cell, or multi-cell - where they return
multiple results from a single formula, entered over multiple cells. When
you selected E2:J2, edited one, and pressed Ctrl+Shift+Enter you told Excel
that the formula in E2 was a multi-cell array formula to be spread over the
selected cells.

What you should have done was to select the cells, then run the code that
you got from Bob. It does the work without any further intervention.

While I'm nagging... I would add that it would be helpful to yourself as
well as others if you stuck to the original thread, so that anyone
referencing it can see all developments.

I wouldn't want you getting us SteveD's a bad name... <bg>

Steve D.