FormulaArray error when entered from VBA

I used the code below to add a Formula Array. I get Error 1004 "Unable to set the FormulaArray propery of the Range class".
http://support.microsoft.com/kb/213181 indicates that this error occurs if the length of the formula exceeds 255 characters. The formula I am trying to enter is length 217.
So, what is the problem here?
Regards
Brian

ub UniqueList()
    Const Title = "SET UP UNIQUE LIST using an array formula"
    With Application
        CalculationSave = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
            SelectionSave = Selection.Address
            Set where = .InputBox("Select the Range using the mouse or accept this range:", Title, Selection.Address, Type:=8)
            If where.Parent.Parent.FullName <> ThisWorkbook.FullName Then GoTo Goodbye
            biglist = "l.i" & (CountNames("l.i") + 1)
            ThisWorkbook.Names.Add Name:=biglist, RefersTo:="='" & where.Parent.Name & "'!" & where.Resize(where.Rows.Count + 1, 1).Address
            Set anchor = .InputBox("Select the Cell  B E L O W  which to place the unique list", Title, Selection.Address, Type:=8)
            If anchor.Parent.Parent.FullName <> ThisWorkbook.FullName Then GoTo Goodbye
            uniqueresult = "a.o" & (CountNames("a.o") + 1)
            ThisWorkbook.Names.Add Name:=uniqueresult, RefersTo:="='" & anchor.Parent.Name & "'!" & anchor.Address
            anchor.Offset(1, 0).Resize(where.Rows.Count, 1).FormulaArray = BigFormula(biglist, uniqueresult)
Goodbye:
        .Calculation = CalculationSave
        .EnableEvents = True
    End With
End Sub
Function BigFormula(listi, anch) As String
    BigFormula = "=IF((ROW()-ROW(" & anch _
     & "))>=SUMPRODUCT(1/COUNTIF(" & listi _
     & "," & listi _
     & "&"""""")),"""""",""""""&INDEX(" & listi _
     & ",SMALL(IF(" & listi _
     & "="""""",ROWS(" & listi _
     & ")-1,IF(MATCH(" & listi _
     & "," & listi _
     & ",0)=ROW(" & listi _
     & ")-CELL(""" & Row & """," & listi _
     & ")+1,ROW(" & listi _
     & ")-CELL(""" & Row & """," & listi _
     & ")+1,ROWS(" & listi _
     & ")-1)),ROW()-ROW(" & anch _
     & "))))"
End Function
Function CountNames(Which)
    Dim myNames As Name
        For Each myname In ThisWorkbook.Names
           If Left(myname.Name, 3) = Which Then CountNames = CountNames + 1
        Next myname
End Function

Open in new window

canesbrAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
andrewssd3Connect With a Mentor Commented:
I haven't gone into the depths of your formula here, but you seem to have too many double quotes in your big formula - you have 6 where you want to insert a null string instead of 4, so you get 3 double quotes in the output. Try:
Function BigFormula(listi, anch) As String
    BigFormula = "=IF((ROW()-ROW(" & anch _
     & "))>=SUMPRODUCT(1/COUNTIF(" & listi _
     & "," & listi _
     & "&"""")),"""",""""&INDEX(" & listi _
     & ",SMALL(IF(" & listi _
     & "="""",ROWS(" & listi _
     & ")-1,IF(MATCH(" & listi _
     & "," & listi _
     & ",0)=ROW(" & listi _
     & ")-CELL(""" & Row & """," & listi _
     & ")+1,ROW(" & listi _
     & ")-CELL(""" & Row & """," & listi _
     & ")+1,ROWS(" & listi _
     & ")-1)),ROW()-ROW(" & anch _
     & "))))"
End Function

Open in new window

I debugged this by breaking on the the failing line, typing:
?BigFormula(biglist, uniqueresult)

Open in new window

in the immediate window, the pasting this into Excel - this gives you a bit of help with what's going wrong!
0
 
canesbrAuthor Commented:
@AndrewSSD3
Thanks - that's it.
Note:  I need  & ")-CELL(""" & "Row" & """," & listi _ in lines 11 and 13.
Note: Once again the error messages and MS articles proved to be of scant help.
Regards
Brian
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.