How to activate array formulas in a range of cells all at the same time?

K

Karenna

The formulas have already been entered, and they are different in eac
cell. Is there a macro that can activate a range of cells when
highlight all of them? If I do that now, it copies one formula to th
rest of the cells. Please let me know if there is any way to do this?

Basically, I'd just like a way around going to each cell and typing F
Cntl-Shift-enter.

Thanks,
Karenn
 
M

mudraker

Kareena

place this code on the worksheet module


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim c As Range
For Each c In Range(Target.Address)
Selection.FormulaArray = c.Formula
Next
End Su
 
K

Ken Wright

As per your other thread:-

Give us an example of the different formulas. I haven't come across any
situation where I would need to do what you are describing, so it's kind of hard
to visualise at the moment. Pick 4 or 5 contiguous cells that you are entering
manually and just copy and paste the formulas from each into the note so we can
see what you are referring to.
 
K

Karenna

Hi Ken

Here is the range of cells

Cell A1
=+AVERAGE(IF(I$12:I$39=1,IF(ISNUMBER(AH$12:AH$39),IF(ISNUMBER(AG$12:AG$39),(AH$12:AH$39-AG$12:AG$39)))))

Cell A2
=+STDEV(IF(I$12:I$39=1,IF(ISNUMBER(AH$12:AH$39),IF(ISNUMBER(AG$12:AG$39),(AH$12:AH$39-AG$12:AG$39)))))

Cell A3
=+MEDIAN(IF(I$12:I$39=1,IF(ISNUMBER(AH$12:AH$39),IF(ISNUMBER(AG$12:AG$39),(AH$12:AH$39-AG$12:AG$39)))))

Cell B1
=+AVERAGE(IF(I$12:I$39=1,IF(ISNUMBER(AI$12:AI$39),IF(ISNUMBER(AH$12:AH$39),(AI$12:AI$39-AH$12:AH$39)))))

Cell B2
=+STDEV(IF(I$12:I$39=1,IF(ISNUMBER(AI$12:AI$39),IF(ISNUMBER(AH$12:AH$39),(AI$12:AI$39-AH$12:AH$39)))))

Cell B3
=+MEDIAN(IF(I$12:I$39=1,IF(ISNUMBER(AI$12:AI$39),IF(ISNUMBER(AH$12:AH$39),(AI$12:AI$39-AH$12:AH$39)))))

it goes on like that for 5 columns total.

I'd like to activate all these at once. There are several groups o
15, so it's time consuming to activate each one, which is why I'
looking for a way to do it en masse.

Thanks,
Karenn
 
Top