Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

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

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Formula Wrapper > Array Formulas
Deze pagina in het Nederlands

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


 


Comments

All comments about this page:


Comment by: Ratesh Sud (9/17/2014 2:13:07 PM)

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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].