Unable to set FormulaArray of Property Class - less than 255 characters

[philk228]

Hello,

I am trying to have the below code entered into Excel via VBA but am getting the "Unable to set the FormulaArray proprty of the range class" on the first line (and I'm assuming I'd continue to get this error on all the other subsequent array formula lines).

Any suggestions on how I might be able to amend the code so that the array formulas are populated into the ranges.
I am using the arrays as a multi-criteria vlookup. Not sure if there is another method might also work.

Range("B9").FormulaArray = "=IFERROR(INDEX(Data!A:AC,MATCH(1,(Data!H:H='Ticker Specific'!A9)*(Data!G:G='Ticker Specific'!$C$3),0),7),"")"
Range("B9:B33").FillDown
Range("C9").FormulaArray = "=IFERROR(INDEX(Data!A:AC,MATCH(1,(Data!H:H='Ticker Specific'!A9)*(Data!G:G='Ticker Specific'!$C$3),0),16),"")"
Range("C9:C33").FillDown
Range("D9").FormulaArray = "=IFERROR(INDEX(Data!A:AC,MATCH(1,(Data!H:H='Ticker Specific'!A9)*(Data!G:G='Ticker Specific'!$C$3),0),14),"")"
Range("D9:D33").FillDown
Range("E9").FormulaArray = "=IFERROR(INDEX(Data!A:AC,MATCH(1,(Data!H:H='Ticker Specific'!A9)*(Data!G:G='Ticker Specific'!$C$3),0),28),"")"
Range("E9:E33").FillDown
Range("F9").FormulaArray = "=IFERROR(INDEX(Data!A:AC,MATCH(1,(Data!H:H='Ticker Specific'!A9)*(Data!G:G='Ticker Specific'!$C$3),0),29),"")"
Range("F9:F33").FillDown




Range("I9").FormulaArray = "=IFERROR(INDEX(Data!A:AC,MATCH(1,(Data!I:I='Ticker Specific'!H9)*(Data!G:G='Ticker Specific'!$C$3),0),7),"")"
Range("I9:I33").FillDown
Range("J9").FormulaArray = "=IFERROR(INDEX(Data!A:AC,MATCH(1,(Data!I:I='Ticker Specific'!H9)*(Data!G:G='Ticker Specific'!$C$3),0),16),"")"
Range("J9:J33").FillDown
Range("K9").FormulaArray = "=IFERROR(INDEX(Data!A:AC,MATCH(1,(Data!I:I='Ticker Specific'!H9)*(Data!G:G='Ticker Specific'!$C$3),0),14),"")"
Range("K9:K33").FillDown
Range("L9").FormulaArray = "=IFERROR(INDEX(Data!A:AC,MATCH(1,(Data!I:I='Ticker Specific'!H9)*(Data!G:G='Ticker Specific'!$C$3),0),28),"")"
Range("L9:L33").FillDown
Range("M9").FormulaArray = "=IFERROR(INDEX(Data!A:AC,MATCH(1,(Data!I:I='Ticker Specific'!H9)*(Data!G:G='Ticker Specific'!$C$3),0),29),"")"
Range("M9:M33").FillDown

 

[Fluff]

You need to double up on the quotes like

Range("B9").FormulaArray = "=IFERROR(INDEX(Data!A:AC,MATCH(1,(Data!H:H='Ticker Specific'!A9)*(Data!G:G='Ticker Specific'!$C$3),0),7),[COLOR=#ff0000]""""[/COLOR])"

 

[philk228]

Thanks! That did the trick.

 

[Fluff]

You're welcome