Long Array Formula - VBA - Please help!

[emily_jane]

Hello!

The maximum amount of characters for a .FormulaArray formula is 255.

I'm trying to get around this by defining the formula as a name and referring to that when making it an array.

My code is as follows:

Dim strLongFormula As String

[COLOR=#444444]strLongFormula = “MAX(IF(‘Quarantine List’!$B$2:$B$” & lastrow2 + 1 & “=’Study Timeline’!A” & lastrow + 1 & “,’Quarantine List’!$D$2:$D$” & lastrow2 + 1 & “))+((MAX(INDIRECT(“”‘Follow Up Appointments’!B””&MATCH(INDEX(‘Quarantine List’!$A$2:$A$” & lastrow2 + 1 & “,MATCH(‘Study Timeline’!A” & lastrow + 1 & “,IF(‘Quarantine List’!$D$2:$D$” & lastrow2 + 1 & “=MAX(IF(‘Quarantine List’!$B$2:$B$” & lastrow2 + 1 & “=’Study Timeline’!A” & lastrow + 1 & “,’Quarantine List’!$D$2:$D$” & lastrow2 + 1 & “)),’Quarantine List’!$B$2:$B$” & lastrow2 + 1 & “),0)),FOLLOW_UP_STUDY,0)&””:G””&MATCH(INDEX(‘Quarantine List’!$A$2:$A$” & lastrow2 + 1 & “,MATCH(‘Study Timeline’!A” & lastrow + 1 & “,IF(‘Quarantine List’!$D$2:$D$” & lastrow2 + 1 & “=MAX(IF(‘Quarantine List’!$B$2:$B$” & lastrow2 + 1 & “=’Study Timeline’!A” & lastrow + 1 & “,’Quarantine List’!$D$2:$D$” & lastrow2 + 1 & “)),’Quarantine List’!$B$2:$B$” & lastrow2 + 1 & “),0)),FOLLOW_UP_STUDY,0)))))”

[/COLOR]
[COLOR=#444444]ThisWorkbook.Names.Add “LongFormula”, “=” & strLongFormula

[/COLOR]
[COLOR=#444444]wsTimeline.Activate

[/COLOR]
[COLOR=#444444]With wsTimeline.Range(“TIMELINE_FOLLOWUP”)(lastrow + 1)

[/COLOR]
[COLOR=#444444].FormulaArray = “=LongFormula”

[/COLOR]
[COLOR=#444444]End With[/COLOR]

The code runs, and the formula appears in the ‘Refers To’ section of the named range. The formula in the string also works, I have copied it from the name manager and replaced the cell and it’s all fine. However my formula in the required cell is just {=LongFormula}.

What am I doing wrong?! Please help, I am rather confused!

 

[ndsutherland]

Nevermind

 

[ndsutherland]

Have you tried taking LongFormula out of the parentheses?

.FormulaArray = "=" & LongFormula