VBA Index Match Array

[Razorramen]

I'm getting a compile error "Expected: end of statement" with "Pass" being highlighted. Any help getting this code to work would be greatly appreciated.

Range("I2").Select
    ActiveCell.FormulaArray= "=IFERROR(Application.WorksheetFunction.Index(Practical!$O$1:$O$10000,Application.WorksheetFunction.Match($A2&"Pass",Practical!$A$1:$A$10001&Practical!$M$1:$M$10001,0)),"")"

Below is the code in the worksheet that works correctly

{=IFERROR(INDEX(Practical!$O$1:$O$10000,MATCH($A2&"Pass",Practical!$A$1:$A$10000&Practical!$M$1:$M$10000,0)),"")}

 

[offthelip]

Your problem is that excel is reading the Double quotes just before the Pass as the end of the string which what you want as a formula. The way round this is to use the ascii character function CHR(34) for double quotes
Something like this (untested)

ActiveCell.FormulaArray= "=IFERROR(Application.WorksheetFunction.Index(Practical!$O$1:$O$10000,Application.WorksheetFunction.Match($A2&" & chr(34) & "Pass" & Chr(34) & ",Practical!$A$1:$A$10001&Practical!$M$1:$M$10001,0))," & chr(34) & chr(34)&")"

 

[Norie]

Try this.

Range("I2").FormulaArray = "=IFERROR(INDEX(Practical!$O$1:$O$10000,MATCH($A2&""Pass"",Practical!$A$1:$A$10000&Practical!$M$1:$M$10000,0)),"""")"