A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Convert Array Formulas to Regular Formulas



 
 
Thread Tools Display Modes
  #1  
Old March 16th, 2010, 08:37 PM posted to microsoft.public.excel.worksheet.functions
Domenick
external usenet poster
 
Posts: 36
Default 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  
Old March 16th, 2010, 09:00 PM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
Default 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  
Old March 16th, 2010, 10:21 PM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 1,896
Default 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  
Old March 18th, 2010, 09:23 PM posted to microsoft.public.excel.worksheet.functions
Domenick
external usenet poster
 
Posts: 36
Default 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  
Old March 19th, 2010, 02:23 PM posted to microsoft.public.excel.worksheet.functions
Domenick
external usenet poster
 
Posts: 36
Default 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?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:49 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.