Try actually looking at what I posted

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
...
Forgot to add, I get Compile Error when I add double quotes to my formula
which becomes this:
Fml = "=SUM(IF(Q9:Q" & (RowIndex - 1) & "<" & ""Rejected"" & ",I9:I" &
(RowIndex - 1) & ",0))"

When I copied your formula, I got Syntax Error though it looks exactly the
same.

"Bob Phillips" wrote:

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