Thanks Gary, I don't know much about RC either, but when i was researching
how to do this the help suggested that you use R1C1 style, so i did..

Altough there were a few "&'s" missing, i got the idea and made it get into
my sheet.

Same probelm though. It needs to be an array. Even using the .FormulaArray
Vs .Formula.

I'm pretty sure that it needs these {} brackets in there, but I'm not sure
where to put them exactly. I've tried a few ways, but it still eludes me.

Anyone else have an idea?

Cheers,

Bam.

"Gary Keramidas" wrote:

didn't do any testing, but this should enter it as a formula. don't know much
about rc addressing. someone else will have to help out.


Range("A2").Formula = "=IF((L3+M3)=0,(INDEX(" & _
Range("$AR$2:$BK$2").Address & ",MATCH(TRUE,SUBTOTAL(9,OFFSET(" & _
Range("$AR3:$BK3").Address & ",,,,COLUMN(" & Range("$AR3:$BK3").Address & _
")-MIN(COLUMN(" & Range("$AR3:$BK3").Address & "))+1))=($J3),0))),INDEX(" &
_
Range("$AR$2:$BK$2").Address & ",MATCH(TRUE,SUBTOTAL(9,OFFSET(" & _
Range("$AR3:$BK3").Address & ",,,,COLUMN(" & Range("$AR3:$BK3").Address & _
")-MIN(COLUMN(" & Range("$AR3:$BK3").Address & "))+1))($L3+$M3),0)))"

--


Gary

"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.