Error in entering a formula
Works for me.
Maybe a bit more explanation on your part would help you to a solution.
NickHK
"Shilps" wrote in message
...
Didn't help :(
"Norman Jones" wrote:
Hi Ships,
See Bob Phillips response in your earlier thread.
Bob has shown you hoe to enter an array formula.
---
Regards,
Norman
"Shilps" wrote in message
...
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!
TIA
|