You nbeed to double up on them

Fml = "=SUM(IF(Q9:Q" & (RowIndex - 1) & "<""Rejected"",I9:I" & (RowIndex -
1) & ",0))"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Shilps" wrote in message
...
Hi,

Would be glad if my other post on the same subject can be answered. That
details the exact problem.

I am quoting the same here again for ease:

Hi,

I am trying to create an array formula through VBA code. I want the
formula(
for a particular parameter) to appear like this in Excel
{=SUM(IF(Q9:Q11<"Rejected",I9:I11,0))}

The code is this
Fml = "=SUM(IF(Q9:Q" & (RowIndex - 1) & "<" & "Rejected" & ",I9:I" &
(RowIndex - 1) & ",0))"
Dsheet.Range(tmp).FormulaArray = Fml

This code makes it look like =SUM(IF(Q9:Q11<Rejected,I9:I11,0)) [
Rejected
without quotes and hence gives error #NAME?

If I change the VBA code to

Fml = "=SUM(IF(Q9:Q" & (RowIndex - 1) & "<" & ' "Rejected" ' & ",I9:I" &
(RowIndex - 1) & ",0))"

OR

Fml = "=SUM(IF(Q9:Q" & (RowIndex - 1) & "<" & " 'Rejected' " & ",I9:I" &
(RowIndex - 1) & ",0))"

it gives error.
I dnt understand what is the problem with quotes!

Thanks





"Dave Peterson" wrote:

or not using an array formula:

somecell.formula = "=sumif(q9:q12,""<""&""Rejected"",i9:i12)"



Shilps wrote:

Hi,

I want to create an array formula in a particular cell, through macro.
I
cannot enter it directly as the cell changes depending on other
parameters.
If I try to enter the formula through macro, it shows the
text-{=SUM(IF(Q9:Q12<'Rejected',I9:I12,0))}, instead of showing the
value.

Pls help.
Thanks

--

Dave Peterson