Long Array Formula VBA

[maacmaac]

I have a formula in cell Q2 as follows

"=MIN(MIN(IF(RIGHT($G$2:$G2,LEN(G2)-FIND(""|"",G2))=(RIGHT(G2,LEN(G2)-FIND(""|"",G2))),($H$2:$H2),"""")),MIN(IF(RIGHT($I$2:$I2,LEN(I2)-FIND(""|"",I2))=(RIGHT(I2,LEN(I2)-FIND(""|"",I2))),($J$2:$J2),"""")),MIN(IF(RIGHT($K$2:$K2,LEN(K2)-FIND(""|"",K2))=(RIGHT(K2,LEN(K2)-FIND(""|"",K2))),($L$2:$L2),"""")))"

I am trying to use this formula in VBA. I have tried

    Range("Q2").FormulaArray = _
        "=MIN(MIN(IF(RIGHT(R2C7:RC7,LEN(RC[-10])-FIND(""|"",RC[-10]))=(RIGHT(RC[-10],LEN(RC[-10])-FIND(""|"",RC[-10]))),(R2C8:RC8),"""")),MIN(IF(RIGHT(R2C9:RC9,LEN(RC[-8])-FIND(""|"",RC[-8]))=(RIGHT(RC[-8],LEN(RC[-8])-FIND(""|"",RC[-8]))),(R2C10:RC10),"""")),MIN(IF(RIGHT(R2C11:RC11,LEN(RC[-6])-FIND(""|"",RC[-6]))=(RIGHT(RC[-6],LEN(RC[-6])-FIND(""|"",RC[-6]))),(R2C12:RC12),"""")))"
    Range("Q2").AutoFill Destination:=Range("Q2:Q35"), Type:=xlFillDefault

I have also tried

    Range("Q2").Formula = "=MIN(MIN(IF(RIGHT($G$2:$G2,LEN(G2)-FIND(""|"",G2))=(RIGHT(G2,LEN(G2)-FIND(""|"",G2))),($H$2:$H2),"""")),MIN(IF(RIGHT($I$2:$I2,LEN(I2)-FIND(""|"",I2))=(RIGHT(I2,LEN(I2)-FIND(""|"",I2))),($J$2:$J2),"""")),MIN(IF(RIGHT($K$2:$K2,LEN(K2)-FIND(""|"",K2))=(RIGHT(K2,LEN(K2)-FIND(""|"",K2))),($L$2:$L2),"""")))"
    Range("Q2").Select
    Application.SendKeys "{F2}^+{ENTER}"
    Range("Q2").AutoFill Destination:=Range("Q2:Q35"), Type:=xlFillDefault

.

I can't get either of these methods to work. I believe there is a 256 char limit for array formulas in VBA, which would explain why the first method won't work. Is there any workaround that might work? Thanks.

[DonkeyOte]

Could you perhaps shorten the Array along the lines of:

Q2:
=MIN(IF(ISTEXT(G2:K2),IF(RIGHT(G$2:K2,LEN(G$2:K2)-FIND("|",G$2:K2))=RIGHT(G2:K2,LEN(G2:K2)-FIND("|",G2:K2)),H$2:L2)))
confirmed with CTRL + SHIFT + ENTER

Given only the row increments you need only hardwire starting row in appropriate range references I think...

[maacmaac]

DonkeyOte,

This works just like the long code and works in VBA. However, I am not coming up with the expected results. This refers back to thread 807004 http://www.excelforum.com/excel-prog...html?p=2670272

Thanks again for your comments.