VBA - Array

MOB · Jan 18, 2012

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 · Jan 18, 2012

You need to double up the quotes inside the formula

Rich (BB code):

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 · Jan 18, 2012

Hi

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

VoG · Jan 18, 2012

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 · Jan 18, 2012

Ahh thanks, didn't know that

Firefly2012 · Jan 18, 2012

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:

Code:

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 · Jan 18, 2012

Firefly2012 said:
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:

Code:

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 · Jan 18, 2012

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.

VBA - Array

MOB

Well-known Member

VoG

Legend

MOB

Well-known Member

VoG

Legend

MOB

Well-known Member

Firefly2012

Well-known Member

MOB

Well-known Member

pgc01

MrExcel MVP

Similar threads

Share this page

VBA - Array

Well-known Member

Legend

Well-known Member

Legend

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock