What wrong is this macro? If I insert this formula using macro , error occurs on

Question

What wrong is this macro?

If I insert this formula using macro, error occurs on array's property.

Does anyone have any suggestions?
Thanks in advance for any suggestions

Sub temp()
Range("B2").FormulaArray = "=IF(ISERROR(SUM(OFFSET(A1,MATCH(1,ABS(A1:A100),0)-1,0,MATCH(-INDEX(A1:A100,MATCH(1,ABS(A1:A100),0)),A1:A100,0)-MATCH(1,ABS(A1:A100),0)))),SUM(A1:A100),SUM(OFFSET(A1,MATCH(1,ABS(A1:A100),0)-1,0,MATCH(-INDEX(A1:A100,MATCH(1,ABS(A1:A100),0)),A1:A100,0)-MATCH(1,ABS(A1:A100),0))))"
End Sub

Thanks for help !

Explanation / Answer

Hi..

The FormulaArray property has a limit of 255 characters (see VBA Help for that property). Your formula exceeds that limit. The R1C1 formula type is not an issue in more recent versions of Excel.

I would suggest either splitting things up, or using a Name. Here is an example of using a Name: Note that I also changed the references to absolute instead of relative. This will ensure they remain the same when you enter them using the Name.

================================

Option Explicit
Sub foo()
Dim WS As Worksheet
Dim V As Variant
Dim I As Long
Dim sForm1 As String
sForm1 = "=IF(ISERROR(SUM(OFFSET($A$1,MATCH(1,ABS($A$1:$A$100),0)-1,0,MATCH(-INDEX($A$1:$A$100,MATCH(1,ABS($A$1:$A$100),0)),$A$1:$A$100,0)-MATCH(1,ABS($A$1:$A$100),0)))),SUM($A$1:$A$100),SUM(OFFSET($A$1,MATCH(1,ABS($A$1:$A$100),0)-1,0,MATCH(-INDEX($A$1:$A$100,MATCH(1,ABS($A$1:$A$100),0)),$A$1:$A$100,0)-MATCH(1,ABS($A$1:$A$100),0))))"

Set WS = ActiveSheet
With WS.Names
    'Check to make sure formula not already there
    On Error Resume Next
        V = .Item("myFormula")
        If Err.Number <> 0 Then .Item("myFormula").Delete
    On Error GoTo 0
    .Add "myFormula", sForm1
End With

Range("b1").FormulaArray = "=myFormula"

End Sub

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.