## Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

FastExcel
The best tool to optimise your Excel model!

What has changed?

This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Articles > Formula Wrapper > Array Formulas # Wrapping Formulas In An Error Test

## 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

Comment by: Ratesh Sud (17-9-2014 14:13:07)

Nice posts. Nice Assistance. Thank's for providing such invaluable assistance and guidance

### Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.