If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Convert Array Formulas to Regular Formulas
On Mar 19, 9:23*pm, Domenick
wrote: 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" error? Thanks. "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 formula. "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 | |
Display Modes | |
|
|