You would also need to detect and handle multi-cell array formulae as well:
This sub may be useful

Sub ExpandRange(oStartRange As Range, oEndRange As Range)

' Input:
' oStartRange,
' a range object that may or may not contain array formulae
' Output:
' oEndRange, a range object that has been expanded -
' to include all the cells in any array formula that is partly in the
range
'
Dim oCell As Range
Dim oArrCell As Range

On Error Resume Next
'
Set oEndRange = oStartRange
For Each oCell In oStartRange
If oCell.HasArray = True Then
For Each oArrCell In oCell.CurrentArray
If Intersect(oEndRange, oArrCell) Is Nothing Then
Set oEndRange = Union(oEndRange, oArrCell)
End If
Next oArrCell
End If
Next oCell
Set oCell = Nothing
Set oArrCell = Nothing
End Sub

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Gary''s Student" wrote in message
...
Your code is good. Just expand it to include array formulas as well:

Sub RecalculationForced()
Dim sF As String, rCell As Range
For Each rCell In ActiveSheet.UsedRange
If rCell.HasFormula = True Then
If Not rCell.HasArray Then
sF = rCell.Formula
rCell.Formula = sF
Else
sF = rCell.FormulaArray
rCell.FormulaArray = sF
End If
End If
Next rCell
End Sub

--
Gary''s Student - gsnu200832


"PBezucha" wrote:

UDFs frequently do not recalculate all from various reasons, especially
after
some manipulation with codes. F9-key combinations are in this case
usually
of no avail, too. A reliable and quick remedy is to replicate all the
formulas (here in an active worksheet) by a macro. In its simplest form,
the
macro fails, however, if it encounters array formula. You can avoid, of
course, that incident:

Sub RecalculationForced()
Dim sF As String, rCell As Range
For Each rCell In ActiveSheet.UsedRange
If rCell.HasFormula = True Then
If Not rCell.HasArray Then
sF = rCell.Formula
rCell.Formula = sF
End If
End If
Next rCell
End Sub

This works well (xl 2002), but afterward you must replicate those omitted
formulas by hand.
Do you guess there can be a programmable way, how to detect the range of
an
array pertinent to the searched cell, and how to arrange its replication?

Sincerely
--
Petr Bezucha