FormulaArray error

[navb]

Hi
I am trying to put a .FormulaArray but since the formula is of more than 225 characters, VBA is showing error. What is the way around for this? I have read a couple of posts describing the same problem where the solution is to use dummy formula and then replace in the original formula. I am new to VBA and haven't been able to make that way around work for me.

Selection.FormulaArray = _
"=INDEX('Estimated Sales Per Day'!R1C1:R[" & lastrow_ws_ESD & "]C[" & lastcol2_ws_ESD & "]),MATCH('Lost Sales Calculation'!RC[-3],'Estimated Sales Per Day'!R2C1:R" & lastrow_ws_ESD & "C1,0),MATCH(VALUE('Lost Sales Calculation'!RC[-4]),'Estimated Sales Per Day'!R1000C2:R1000C [" & lastcol2_ws_ESD & "],0))"
Thanks in advance
Your help will be greatly appreciated

 

[VoG]

This example may help you http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

 

[CharlesChuckieCharles]

its actually only 214 long

"=INDEX('Estimated Sales Per Day'!R1C1:R[" & lastrow_ws_ESD & "]C[" & lastcol2_ws_ESD & "]),MATCH('Lost Sales Calculation'!RC[-3],'Estimated Sales Per Day'!R2C1:R" & lastrow_ws_ESD & "C1,0),MATCH(VALUE('Lost Sales Calculation'!RC[-4]),'Estimated Sales Per Day'!R1000C2:R1000C [" & lastcol2_ws_ESD & "],0))"

But the bracket in bold ... is suspect

may need another look