Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert Array Formulas to Regular Formulas
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert Array Formulas to Regular Formulas
STEP 1: MOST IMPORTANT -- make a copy of the troublesome workbook and test
this in the copy, not the original! Using the copy of the workbook, put this code into a regular code module and run it. It may take a while depending on the number of sheets in the workbook and the number of cells used on each one, because it's going to examine every cell on every sheet that was ever used! Here's the code: to put it into the workbook, open the workbook, then press [Alt]+[F11] to enter the VB Editor (VBE). Copy and paste the code into the module. At that point you can actually place the cursor anywhere within the code and just press the [F5] key to run it right then and there. Sub KillArrayFormulas() Dim anySheet As Worksheet Dim sheetUsedRange As Range Dim anyCell As Range For Each anySheet In ThisWorkbook.Worksheets Set sheetUsedRange = anySheet.UsedRange For Each anyCell In sheetUsedRange If anyCell.HasFormula Then anyCell.Formula = anyCell.Formula End If Next Next 'some housekeeping Set sheetUsedRange = Nothing Set anySheet=Nothing MsgBox"Formula Rewrites Completed" End Sub "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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert Array Formulas to Regular Formulas
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert Array Formulas to Regular Formulas
I see what you mean to do, but when I run your code, I get "Error 1004: You
cannot change part of an array." Any ideas how to work around this? -Dom "JLatham" wrote: STEP 1: MOST IMPORTANT -- make a copy of the troublesome workbook and test this in the copy, not the original! Using the copy of the workbook, put this code into a regular code module and run it. It may take a while depending on the number of sheets in the workbook and the number of cells used on each one, because it's going to examine every cell on every sheet that was ever used! Here's the code: to put it into the workbook, open the workbook, then press [Alt]+[F11] to enter the VB Editor (VBE). Copy and paste the code into the module. At that point you can actually place the cursor anywhere within the code and just press the [F5] key to run it right then and there. Sub KillArrayFormulas() Dim anySheet As Worksheet Dim sheetUsedRange As Range Dim anyCell As Range For Each anySheet In ThisWorkbook.Worksheets Set sheetUsedRange = anySheet.UsedRange For Each anyCell In sheetUsedRange If anyCell.HasFormula Then anyCell.Formula = anyCell.Formula End If Next Next 'some housekeeping Set sheetUsedRange = Nothing Set anySheet=Nothing MsgBox"Formula Rewrites Completed" End Sub "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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert Array Formulas to Regular Formulas
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? |
#6
|
|||
|
|||
Quote:
I would need the opposite of this but cant seem to figure out how that could be done. I have a huge range and need to convert all the formulas to array formulas to get the desire results there. Thanks in advance! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert Array Formulas to Regular Formulas
Hi,
I have a sample excel file which contains a lot of array formulas which has made the spreadsheet extremely slow. Is there a way to remove the array formulas from SHEET 1 convert them into normal formulas and still achieve the same result ? If any one of can please help with it ? The formulas which I want to convert are : =IFERROR(INDEX(Detail!A3:A$1500,MATCH("Old Stock Issue",Detail!P3:P$1500,0)),"") =IFERROR(INDEX($B$2:$B$1500,MATCH(0,INDEX(COUNTIF( $D$1:D1,$B$2:$B$1500),0,0),0)),"") =IFERROR(INDEX(Detail!A3:A$1500,MATCH(TRUE,ISBLANK (Detail!P3:P$1500),0)),"") =IFERROR(INDEX($F$2:$F$1500,MATCH(0,INDEX(COUNTIF( $G$1:G1,$F$2:$F$1500),0,0),0)),"") =INDEX($G$2:$G$1500,SMALL(IF(H$2:H$1500=$K21,ROW($ G$2:$G$1500)-ROW($G$2)+1),COUNTIF($K$2:$K21,$K21))) Many Thanks, Biswajit |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formulas | Excel Worksheet Functions | |||
Formulas with-an an array | Excel Worksheet Functions | |||
Array formulas convert to zero when e-mailing | Excel Discussion (Misc queries) | |||
Answer's a little more complicated than regular formulas, but what do I need to do? | Excel Discussion (Misc queries) | |||
Array formulas | Excel Worksheet Functions |