LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 21st 10, 05:55 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
First recorded activity by ExcelBanter: Nov 2009
Posts: 30
Default Convert Array Formulas to Regular Formulas

On Mar 19, 9:23*pm, Domenick
When I manually correct these spreadsheets, I have to select all the cells
that have the array formulas and DELETE them before I can go ahead and paste
in the regular formulas from another workbook. Is there some way to modify
your code to do this so that I don't get the "Cannot change part of an array"


"JLatham" wrote:
I meant to confirm that the {} you are seeing in array formulas are placed
there by Excel. *They are placed there when you commit a formula using
[Ctrl]+[Shift]+[Enter] rather than by the normal [Enter] key. *So you're
right, in a fashion they don't really exist; kind of like the $ symbol when
you format a cell as currency - you didn't put it there, Excel did.

Any editing of an array formula requires that you again commit it by using
the 3-key combination, but if you just hit [Enter] it turns into a non-array

"Domenick" wrote:

I have a workbook that is linked to a query from another (legacy)
application. I have formulas that do further calculations with the query
results. Everything worked fine in Excel 2003. However, I recently upgraded
to Excel 2007 and now, all of the formulas that work on the results of the
query turn into ARRAY formulas (and they should just be regular formulas).
I've given up trying to figure out what is causing the issue as the legacy
application is no longer supported. I just want to write a macro to fix all
of the formulas and convert them back into regular formulas. I've tried doing
a find and replace for the "{" and "}" characters in the array formulas, but
Excel never finds anything to replace (I assume the physical characters
aren't really there).

Any suggestions on how I could convert all array formulas on a worksheet to
standard formulas?

select any one cell within the array formula, press F2 to edit, press
CTRL+SHIFT+ENTER, this will help u to select all the cells within the
array formula,
then press F2 again, press CTRL+ENTER, this will split the array
formula into standard formula in each cell.
this might be what u'r looking for.

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Array Formulas to Regular Formulas Domenick Excel Worksheet Functions 6 August 17th 15 09:16 PM
array formulas using MIN MAX Christi Excel Worksheet Functions 3 December 17th 08 06:53 PM
Array Formulas Peledon Excel Worksheet Functions 2 February 9th 07 12:21 PM
Array formulas convert to zero when e-mailing [email protected] Excel Discussion (Misc queries) 3 December 12th 06 06:13 PM
Answer's a little more complicated than regular formulas, but what do I need to do? Jordan Smith Excel Discussion (Misc queries) 3 April 19th 06 06:51 AM

All times are GMT +1. The time now is 01:11 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.

About Us

"It's about Microsoft Excel"


Copyright © 2017