Writing Array formula through macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Try something like

Activecell.FormulaArray = "=SUM(IF(Q9:Q12<>""Rejected"",I9:I12,0))"

Note that you don't enter the braces.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
or not using an array formula:

somecell.formula = "=sumif(q9:q12,""<>""&""Rejected"",i9:i12)"
 
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
 
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 said:
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 said:
or not using an array formula:

somecell.formula = "=sumif(q9:q12,""<>""&""Rejected"",i9:i12)"
Click to expand...
Click to expand...
 
Hi Bob,

Tired this taking a clue from your earlier reply, but it gives Compile
Error: Expected end of Statement

Thanks

Bob Phillips said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
That's one of the bad things about multiposting the same question (or variations
of the same question). You can get answers all over the place.

I thought that the =sumif() suggestions in each thread answered both versions of
the question.

But maybe not. You didn't reply to the other post.
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 said:
or not using an array formula:

somecell.formula = "=sumif(q9:q12,""<>""&""Rejected"",i9:i12)"
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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





:

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
Click to expand...
Click to expand...
Click to expand...
 
Syntax error :(

Bob Phillips said:
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 said:
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 said:
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)

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





:

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
Click to expand...
Click to expand...
Click to expand...
 
With e,g, RowIndex =2, I get:
=SUM(IF(Q9:Q1<>"Rejected",I9:I1,0))
so this works:
Range("H11").FormulaArray = "=SUM(IF(Q9:Q" & (RowIndex - 1) &
"<>""Rejected"",I9:I" & (RowIndex - 1) & ",0))"

What do you expect ?

NickHK

Shilps said:
Syntax error :(
Click to expand...
-------------- CUT --------------------
 
Thanks. It worked.

NickHK said:
With e,g, RowIndex =2, I get:
=SUM(IF(Q9:Q1<>"Rejected",I9:I1,0))
so this works:
Range("H11").FormulaArray = "=SUM(IF(Q9:Q" & (RowIndex - 1) &
"<>""Rejected"",I9:I" & (RowIndex - 1) & ",0))"

What do you expect ?

NickHK


-------------- CUT --------------------
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

Error in entering a formula 5
array formula in macro 3
VBA - Multiple Array-Enter Command 1
Formula appearing as Value in Formula Bar 1
Array Formula In IF Function 0
array formula question 4
Excel formula, sum the value cell and if empty cell left blank 0
Convert Normal formula to array formula 4

Back
Top