VBA - Array

[MOB]

I'm creating a simple bit of code which needs to include an array formula - but the code below fails;

Selection.FormulaArray = _
"=IF($L3="","",IF(ISERROR(INDEX(Data!$BG$7:$BP$11,MATCH('Pay Advice'!$A$2,Data!$A$7:$A$11,0),MATCH(1,IF(Data!$BG$6:$BP$6='Pay Advice'!$L3,IF(Data!$BG$5:$BP$5='Pay Advice'!H$11,1)),0))),0,INDEX(Data!$BG$7:$BP$11,MATCH('Pay Advice'!$A$2,Data!$A$7:$A$11,0),MATCH(1,IF(Data!$BG$6:$BP$6='Pay Advice'!$L3,IF(Data!$BG$5:$BP$5='Pay Advice'!H$11,1))))))"

"Runtime error 1004
Unable to set the FormulaArray property of the range class"

What am I doing wrong? The formula is fine when typed in.

TIA

 

[VoG]

You need to double up the quotes inside the formula

Selection.FormulaArray = _
"=IF($L3="""","""",IF(ISERROR(INDEX(Data!$BG$7:$BP$11,MATCH('Pay Advice'!$A$2,Data!$A$7:$A$11,0),MATCH(1,IF(Data!$BG$6:$BP$6='Pay Advice'!$L3,IF(Data!$BG$5:$BP$5='Pay Advice'!H$11,1)),0))),0,INDEX(Data!$BG$7:$BP$11,MATCH('Pay Advice'!$A$2,Data!$A$7:$A$11,0),MATCH(1,IF(Data!$BG$6:$BP$6='Pay Advice'!$L3,IF(Data!$BG$5:$BP$5='Pay Advice'!H$11,1))))))"

 

[MOB]

Hi

Still getting the same error message even after copying your code above................

 

[VoG]

The formula exceeds the 255 character limit. This may help you http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

 

[MOB]

Ahh thanks, didn't know that

 

[Firefly2012]

FormulaArray can accept a maximum length of 255 characters - your formula exceeds this. What you could do is use the Replace method of the range object to facilitate:

Dim sFormula1 As String, sFormula2 As String
 
sformula1 = "=IF($L3="""","""",IF(ISERROR(INDEX(Data!$BG$7:$BP$11,MATCH('Pay Advice'!$A$2,Data!$A$7:$A$11,0),MATCH(1,IF(Data!$BG$6:$BP$6='Pay Advice'!$L3,IF(Data!$BG$5:$BP$5='Pay Advice'!H$11,1)),0))),0,XXX()))"
sformula2 = "INDEX(Data!$BG$7:$BP$11,MATCH('Pay Advice'!$A$2,Data!$A$7:$A$11,0),MATCH(1,IF(Data!$BG$6:$BP$6='Pay Advice'!$L3,IF(Data!$BG$5:$BP$5='Pay Advice'!H$11,1))))"
 
Selection.FormulaArray = sformula1

Selection.Replace "XXX()", sformula2

Effectively you use a placeholder and then replace that with the rest of your formula.

 

[MOB]

FormulaArray can accept a maximum length of 255 characters - your formula exceeds this. What you could do is use the Replace method of the range object to facilitate:

Dim sFormula1 As String, sFormula2 As String
 
sformula1 = "=IF($L3="""","""",IF(ISERROR(INDEX(Data!$BG$7:$BP$11,MATCH('Pay Advice'!$A$2,Data!$A$7:$A$11,0),MATCH(1,IF(Data!$BG$6:$BP$6='Pay Advice'!$L3,IF(Data!$BG$5:$BP$5='Pay Advice'!H$11,1)),0))),0,XXX()))"
sformula2 = "INDEX(Data!$BG$7:$BP$11,MATCH('Pay Advice'!$A$2,Data!$A$7:$A$11,0),MATCH(1,IF(Data!$BG$6:$BP$6='Pay Advice'!$L3,IF(Data!$BG$5:$BP$5='Pay Advice'!H$11,1))))"
 
Selection.FormulaArray = sformula1

Selection.Replace "XXX()", sformula2

Effectively you use a placeholder and then replace that with the rest of your formula.

Thanks, extremely helpful

 

[pgc01]

Hi

In this case you can also change the formula to non-array, and you can write it directly in the cell.

Change the If(Condition,1) to Index(Condition,0). This doesn't need cse to result in an array.