| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing Array formula through macro
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Writing Array formula through macro
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) "Shilps" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Writing Array formula through macro
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Writing Array formula through macro
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Writing Array formula through macro
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Writing Array formula through macro
Hi Bob,
Tired this taking a clue from your earlier reply, but it gives Compile Error: Expected end of Statement Thanks "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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Writing Array formula through macro
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Writing Array formula through macro
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. Shilps wrote: 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 -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Writing Array formula through macro
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Writing Array formula through macro
Syntax error :(
"Bob Phillips" wrote: 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Writing Array formula through macro
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" wrote in message ... Syntax error :( "Bob Phillips" wrote: 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 -------------- CUT -------------------- |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Writing Array formula through macro
Thanks. It worked.
"NickHK" wrote: 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" wrote in message ... Syntax error :( "Bob Phillips" wrote: 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 -------------- CUT -------------------- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel: VB Macro programming problem with formula writing | Excel Programming | |||
| Writing a simple macro or formula | Excel Programming | |||
| Writing a range to an array... | Excel Programming | |||
| VB Macro writing a formula to a cell | Excel Programming | |||
| Prb: Writing Array functions in VBA | Excel Programming | |||
![Shilps[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
