Tags: activate, array, cell, cells, eac, entered, excel, formulas, highlight, macro, microsoft, msdn, range, software, time

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

On Microsoft » Microsoft Excel

3,508 words with 3 Comments; publish: Fri, 23 May 2008 08:00:00 GMT; (30662.50, « »)

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

--

Message posted from http://www.ExcelForum.com

All Comments

Leave a comment...

  • 3 Comments
    • 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

      --

      Message posted from http://www.ExcelForum.com

      #1; Fri, 23 May 2008 08:01:00 GMT
    • 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.

      --

      Regards

      Ken.................. Microsoft MVP - Excel

      Sys Spec - Win XP Pro / XL 00/02/03

      ----

      It's easier to beg forgiveness than ask permission :-)

      ----

      "Karenna >" <<Karenna.12eqvk.excel.todaysummary.com.excelforum-nospam.com> wrote in message

      news:Karenna.12eqvk.excel.todaysummary.com.excelforum-nospam.com...

      > The formulas have already been entered, and they are different in each

      > cell. Is there a macro that can activate a range of cells when I

      > highlight all of them? If I do that now, it copies one formula to the

      > 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 F2

      > Cntl-Shift-enter.

      > Thanks,

      > Karenna

      >

      > --

      > Message posted from http://www.ExcelForum.com/

      >

      Outgoing mail is certified Virus Free.

      Checked by AVG anti-virus system (http://www.grisoft.com).

      Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004

      #2; Fri, 23 May 2008 08:02:00 GMT
    • 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

      --

      Message posted from http://www.ExcelForum.com

      #3; Fri, 23 May 2008 08:03:00 GMT