emily_jane
New Member
- Joined
- Feb 12, 2014
- Messages
- 3
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:
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!
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:
Code:
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!