Formula Array - Syntax Error

[Bam]

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,OFFSET(RC44:RC63,,,,COLUMN(RC44:RC63)-MIN(COLUMN(RC44:RC63))+1))>=(RC10),0))),INDEX(R2C44: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,SUBTOTAL(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,,,,COLUMN($AR3:$BK3)-MIN(COLUMN($AR3:$BK3))+1))>($L3+$M3),0)))

Thanks in advance?

Bam.

 

[Rick Rothstein]

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.

 

[Gary Keramidas]

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

 

[Bam]

Rick,

I'm not actually using any variables.

This is an Array Formula that when i refresh my data, the formula is put
into the sheet from the 1st row to the last used row.

If i put my formula straight into the cell, it comes up #N/A because it
needs to be an Array - Ie: Ctrl+ Shift + Enter {}.

My problem is that when trying to put this Array in through vba, i am fairly
sure that I am missing { } brackets in the formula. I just don't know where
they need to be put.

Does that make sense?

Thanks,

Bam

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)

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,OFFSET(RC44:RC63,,,,COLUMN(RC44:RC63)-MIN(COLUMN(RC44:RC63))+1))>=(RC10),0))),INDEX(R2C44: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,SUBTOTAL(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,,,,COLUMN($AR3:$BK3)-MIN(COLUMN($AR3:$BK3))+1))>($L3+$M3),0)))

Thanks in advance?

Bam.

 

[Bam]

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.

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

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,OFFSET(RC44:RC63,,,,COLUMN(RC44:RC63)-MIN(COLUMN(RC44:RC63))+1))>=(RC10),0))),INDEX(R2C44: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,SUBTOTAL(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,,,,COLUMN($AR3:$BK3)-MIN(COLUMN($AR3:$BK3))+1))>($L3+$M3),0)))

Thanks in advance?

Bam.