Error in entering a formula

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

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
 
Hi Ships,

See Bob Phillips response in your earlier thread.

Bob has shown you hoe to enter an array formula.
 
Didn't help :(

Norman Jones said:
Hi Ships,

See Bob Phillips response in your earlier thread.

Bob has shown you hoe to enter an array formula.


---
Regards,
Norman


Shilps said:
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
Click to expand...
Click to expand...
 
Hi Ships,

'-----------------
See Bob Phillips response in your earlier thread.
Click to expand...
Bob has shown you hoe to enter an array formula.
Click to expand...
Didn't help :(
Click to expand...
'-----------------

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
 
Works for me.
Maybe a bit more explanation on your part would help you to a solution.

NickHK

Shilps said:
Didn't help :(

Norman Jones said:
Hi Ships,

See Bob Phillips response in your earlier thread.

Bob has shown you hoe to enter an array formula.


---
Regards,
Norman


Shilps said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...
 
Back
Top