Pages in this article
-
Formula Wrapper
-
Array Formulas
Enable For Array formulas
Of course the first attempt was far from complete. If any cell in the
range contains an array formula, the subroutine errors out.
Accounting for array formulas is a bit more complicated.
First of all, we need to ensure a range containing a single array
formula is treated as a single entity. Secondly, we need to keep track
of the ranges with a single array formula so we don't try to convert
their formula more than once. The code below does all that:
Sub ChangeFormulas()
Dim oCell As Range
Dim sFormula As String
Dim sInput As String
Dim oDone As Range
Dim bFirst As Boolean
Static sFormulaTemplate As String
If sFormulaTemplate = "" Then
sFormulaTemplate =
"=IF(ISERROR(_form_),"""",_form_)"
End If
sInput = InputBox("Enter base formula", , sFormulaTemplate)
If sInput = "" Then Exit Sub
sFormulaTemplate = sInput
For Each oCell In Selection
sFormula = Replace(sFormulaTemplate,
"_form_", Right(oCell.Formula, _
Len(oCell.Formula) - IIf(Left(oCell.Formula, 1) = "=", 1, 0)))
If bFirst = False Then
bFirst = True
Set oDone =
oCell
If
oCell.HasArray Then
oCell.CurrentArray.FormulaArray = sFormula
Set oDone = Union(oDone, oCell.CurrentArray)
Else
oCell.Formula = sFormula
Set oDone = Union(oDone, oCell)
End If
ElseIf Intersect(oDone, oCell) Is
Nothing Then
If
oCell.HasArray Then
oCell.CurrentArray.FormulaArray = sFormula
Set oDone = Union(oDone, oCell.CurrentArray)
Else
oCell.Formula = sFormula
Set oDone = Union(oDone, oCell)
End If
End If
Next
End Sub