Rebuild Array-formulas
 

Hi There,

Array-formulas are incredibly useful ... although most people are
impressed with the power of it, they have serious difficulties to
understand that they need to rebuild them with CTRL+SHIFT+Enter.

Therefo

I would like to rebuild to be able to rebuild my Array-formulas
automatically. It jams though ..

Sub Rebuild_ArrayFormula()
Dim cells As Long, i As Long
Dim rngcel As Range

cells = Selection.Cells.Count
i = 1
For Each rngcel In Selection
rngcel.FormulaArray = rngcel.Formula '<====jams up here ...
Next
End Sub

Rebuild Array-formulas
 

This code, which is virtually identical you yours, ran for me:

Dim Cell As Range
For Each Cell In Selection
Cell.FormulaArray = Cell.Formula
Next


--
Jim
"Sige" wrote in message
oups.com...
| Hi There,
|
| Array-formulas are incredibly useful ... although most people are
| impressed with the power of it, they have serious difficulties to
| understand that they need to rebuild them with CTRL+SHIFT+Enter.
|
| Therefo
|
| I would like to rebuild to be able to rebuild my Array-formulas
| automatically. It jams though ..
|
| Sub Rebuild_ArrayFormula()
| Dim cells As Long, i As Long
| Dim rngcel As Range
|
| cells = Selection.Cells.Count
| i = 1
| For Each rngcel In Selection
| rngcel.FormulaArray = rngcel.Formula '<====jams up here ...
| Next
| End Sub
|

Rebuild Array-formulas
 

Hi Jim,

Thanks! Glad to hear that it works for you! At least I am going in the
right direction.
But would it surprise you if I say it jams now on?:

Cell.FormulaArray = Cell.Formula

;o)

I have no idea why it jams... (XL97-SR2)
Sige

Rebuild Array-formulas
 

If you have a multicell array formula, then it would cause problems if you
try to change a single cell in the array.

if it is a single cell array formula as tested by Jim, then, as he has
shown, it will work.

--
Regards,
Tom Ogilvy

"Sige" wrote in message
oups.com...
Hi Jim,

Thanks! Glad to hear that it works for you! At least I am going in the
right direction.
But would it surprise you if I say it jams now on?:

Cell.FormulaArray = Cell.Formula

;o)

I have no idea why it jams... (XL97-SR2)
Sige

Rebuild Array-formulas
 

Tom raises a good point. If you're array entering a single (identical)
formula in a range I think you can forget the loop and just do:

Select.FormulaArray = ActiveCell.Formula

--
Jim
"Sige" wrote in message
oups.com...
| Hi Jim,
|
| Thanks! Glad to hear that it works for you! At least I am going in the
| right direction.
| But would it surprise you if I say it jams now on?:
|
| Cell.FormulaArray = Cell.Formula
|
| ;o)
|
| I have no idea why it jams... (XL97-SR2)
| Sige
|

Rebuild Array-formulas
 

Thanks Tom,

Is it possible to adjust the code to make it work for multicell
array-formulas?

Sige

Rebuild Array-formulas
 

Sige,

Below is some code that I wrote a long time ago (as a learning exercise) that updates any type of
formula in a workbook by changing a string (which could be a cell address, etc) within the formula.

You can at least see how to check for different types of formulas, and how to re-enter them.

HTH,
Bernie
MS Excel MVP

Option Explicit

'Declare constants for ReturnFormulaType function - assigned to FType
Const conMCAF As Integer = 1 ' con-stant Multi Cell Array Formula
Const conSCAF As Integer = 2 ' con-stant Single Cell Array Formula
Const conRF As Integer = 3 ' con-stant Regular Formula
Const conNF As Integer = 4 ' con-stant Non Formula
Public Const BoxTitle As String = "Formula Updater" ' Title for message boxes

'Dimension Global Variables
Dim FType As Integer
Dim myCell As Range

Sub UpdateFormulas()
Dim OldString As String
Dim NewString As String
Dim mySht As Worksheet

OldString = InputBox("Enter string to replace", BoxTitle)
NewString = InputBox("Enter replacement string", BoxTitle)

On Error Resume Next

For Each mySht In ActiveWorkbook.Worksheets
For Each myCell In mySht.Cells.SpecialCells(xlCellTypeFormulas)

If InStr(1, myCell.Formula, OldString) 1 Then

'Find out what type of formula it is
FType = ReturnFormulaType(myCell)

'Cell has a regular formula
If FType = conRF Then
myCell.Formula = _
Application.Substitute(myCell.Formula, _
OldString, NewString)
End If

'Cell has a Single Cell Array Formula
If FType = conSCAF Then
myCell.FormulaArray = _
Application.Substitute(myCell.FormulaArray, _
OldString, NewString)
End If

'Cell has a Multi-Cell Array Formula (is part of an array)
If FType = conMCAF Then
myCell.CurrentArray.FormulaArray = _
Application.Substitute(myCell.CurrentArray.Formula Array, _
OldString, NewString)
End If

End If

Next myCell

Next mySht

End Sub

Private Function ReturnFormulaType(inCell As Range) As Integer
'Function returns the type of formula in the passed cell
If inCell.HasArray Then
If inCell.CurrentArray.Address < inCell.Address Then
ReturnFormulaType = conMCAF 'Multi-Cell Array Formula
Else
ReturnFormulaType = conSCAF 'Single-Cell Array Formula
End If
Else
If inCell.HasFormula Then
ReturnFormulaType = conRF 'Regular Formula
Else
ReturnFormulaType = conNF 'No Formula
End If
End If
End Function









"Sige" wrote in message
ups.com...
Thanks Tom,

Is it possible to adjust the code to make it work for multicell
array-formulas?

Sige

Rebuild Array-formulas
 

Hi Bernie,

Cool funtion!
I do not manage to replace a string in a multi cell array formula
though. :o(