Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in entering a formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in entering a formula
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in entering a formula
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in entering a formula
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 --- Regards, Norman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in entering a formula
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 Shilps wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Entering Formula - Cannot select cell as it is hidden by formula | Excel Worksheet Functions | |||
Help - error entering data into cell | Excel Worksheet Functions | |||
error message 91 when entering wrong data | Excel Programming | |||
Error when entering a formula | Excel Programming | |||
Error when entering and exiting excel | Excel Discussion (Misc queries) |