Error in entering a formula

[Guest]

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

 

[Norman Jones]

Hi Ships,

See Bob Phillips response in your earlier thread.

Bob has shown you hoe to enter an array formula.

 

[Guest]

Didn't help

Hi Ships,

See Bob Phillips response in your earlier thread.

Bob has shown you hoe to enter an array formula.


---
Regards,
Norman

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

 

[Norman Jones]

Hi Ships,

'-----------------

See Bob Phillips response in your earlier thread.

Bob has shown you hoe to enter an array formula.

Didn't help

'-----------------

Then the polite (and most efficient) course of action
would be to post an explantory response in that thread.

Personally, I find that Bob's reponses are invariably
extremely useful

 

[NickHK]

Works for me.
Maybe a bit more explanation on your part would help you to a solution.

NickHK

Didn't help

Hi Ships,

See Bob Phillips response in your earlier thread.

Bob has shown you hoe to enter an array formula.


---
Regards,
Norman

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

 

[Dave Peterson]

You could use =sumif():

Option Explicit
Sub testme()
Dim RowIndex As Long
Dim myCell As Range

With ActiveSheet
Set myCell = .Range("a1") 'something
RowIndex = 32 'something
myCell.Formula _
= "=sumif(q9:q1" & RowIndex - 1 _
& ",""<>""&""Rejected"",i9:i" & RowIndex - 1 & ")"
End With
End Sub

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