Formula Array - Syntax Error

B

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

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

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

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

Rick Rothstein said:
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 said:
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.
Click to expand...
Click to expand...
 
B

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.

Gary Keramidas said:
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 said:
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.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

INDEX - MATCH - OFFSET By Reference 2
Code with formula interfering in worksheet change event 2
Using Filter string with IF function 9
Copy a R1C1 formula to a user defined range results #NAME? error 2
get value from last row of an autofilter array 2
LookUp (Date) Value from Forecast Data - From 2nd Sheet 3
User input formula variable 3
Formula to Count and Return Most common Value in a Dynamic Named Range 1

Top