Everything between quote marks is considered pure text... you have
everything between quotes... so, what you think is a range reference, VB
sees as a bunch of letters, numbers and brackets. Anything that is a
reference or variable must be concatenated with the rest of the text in
order for VB to see it as something other than text characters. Without
pulling your formula apart (I'll leave that messy work for you to do), let
me give you an example. Say you have a variable named MyVariable and it
contains the value 123. If you do this...

MsgBox "There are MyVariable marbles in the bag."

then VB will display a MessageBox with exactly that text... it will not see
MyVariable as a variable... in the above text string, MyVariable is no
different than the word "are" before it or the word "marbles" after it...
they are all inside the quote marks so VB sees them as nothing more than
text. Now, if you did this...

MsgBox "There are " & MyVariable & "marbles in the bag."

then MyVariable is no longer inside the quoted text and VB can see it as the
variable that it is and will therefore access the value it contains. In this
case, the MessageBox will display the text "There are 123 marbles in the
bag" as expected. You will need to break your text up in accordance with the
above idea in order to get what you are looking for.

--
Rick (MVP - Excel)


"Bam" wrote in message
...
Hi All,

I am trying to put my array formula into my spreadsheet through vba and
can't figure out the correct syntax for it.
It works fine if i leave the formula on the worksheet.

Can you help me??

MySheet.Range(Cells(3, 8), Cells(myrowcount, 8)).FormulaArray =
"=IF((RC[4]+RC[5])=0,(INDEX(R2C44:R2C63,MATCH(TRUE,SUBTOTAL(9,OFFSE T(RC44:RC63,,,,COLUMN(RC44:RC63)-MIN(COLUMN(RC44:RC63))+1))=(RC10),0))),INDEX(R2C4 4:R2C63,MATCH(TRUE,SUBTOTAL(9,OFFSET(RC44:RC63,,,, COLUMN(RC44:RC63)-MIN(COLUMN(RC44:RC63))+1))(RC12+RC13),0)))"

Or

=IF((L3+M3)=0,(INDEX($AR$2:$BK$2,MATCH(TRUE,SUBTOT AL(9,OFFSET($AR3:$BK3,,,,COLUMN($AR3:$BK3)-MIN(COLUMN($AR3:$BK3))+1))=($J3),0))),INDEX($AR$2 :$BK$2,MATCH(TRUE,SUBTOTAL(9,OFFSET($AR3:$BK3,,,,C OLUMN($AR3:$BK3)-MIN(COLUMN($AR3:$BK3))+1))($L3+$M3),0)))

Thanks in advance?

Bam.