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
|