473,224 Members | 1,434 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

End Of Statement Error with VBA formula for Excel


I'm simply trying to put a formula in a range of cells in excel using VBA. I have the formula below:
Expand|Select|Wrap|Line Numbers
  1. Sub TimeSpentFormula()
  2.     Range("SR_TimeSpent").Formula = _
  3.       "=IF(OR(SR_DateClosed="CLOSED", _
  4.       SR_DateClosed = "ON HOLD", _
  5.       Resolution = "Open"), _
  6.       "For Confirmation", _
  7.       (SR_DateClosed-SR_DateOpened))"
  8. End Sub
But I get an error "End of Statement" with the word "CLOSED" highlighted in blue. I think it's considering the first quotation mark beside the word "CLOSED" as the end of the whole formula. Any idea how I can avoid this? Thanks for your help.
Sep 22 '12 #1
5 2137
5,501 Expert Mod 4TB

When posting any code please select the posted code and click on the <CODE/> button. Refer the posting guidlines.

As for your error... you are using double quotes ( " ) within the string... you need to replace those with single quotes ( ' ):

So it should read (in part:
"=IF(OR(SR_DateClosed="CLOSED", _
SR_DateClosed = "ON HOLD"

Change to read:
"=IF(OR(SR_DateClosed='CLOSED', _
SR_DateClosed = 'ON HOLD'

and so forth
Sep 22 '12 #2
Try this

Expand|Select|Wrap|Line Numbers
  1. Sub TimeSpentFormula()
  2.       Range("SR_TimeSpent").Formula = _
  3.        "=IF(OR(SR_DateClosed=""CLOSED""," & _
  4.        "SR_DateClosed = ""ON HOLD""," & _
  5.        "Resolution = ""Open"")," & _
  6.        """For Confirmation""," & _
  7.        "(SR_DateClosed-SR_DateOpened))"
  8. End Sub
Personally i would just write it out instead of trying to separate into multiple lines.
Sep 24 '12 #3
5,501 Expert Mod 4TB
In this case, "Doubling", "Escaping", or "Embedding" the double quotes makes for messier code and places an additional loading on the parser.

The single quote replacements as I sugested is the given best practice:
Sep 24 '12 #4
I quote from Neopa's post,

"A string in VBA requires the Double-Quote (") to delimit it. Single-Quotes (') won't work."

If you try creating a formula in excell with single quotes it will not work because it doesn't convert it to a string, and the formula fails.
Sep 24 '12 #5
5,501 Expert Mod 4TB
you're absolutly correct, for some reason I didn't read it as an excel formula.... been working in the Access mode for too long.
Sep 24 '12 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Igor | last post by:
Hi everyone! I have a program written on C# that works with Microsoft Excel application. I built it to work with Excel 2000. With runtime objects I created assemblies to connect to Excel....
by: Kevin R. | last post by:
Greetings, I have the following code that attempts to download an excel file: System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; ...
by: BaWork | last post by:
I have the following to insert a new record: <% .. Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open "DSN=qqqqq;" SQLStmt = "INSERT INTO tbl_qqqqqq (main_cat, cat_fee, fee_amount)...
by: leaf0209 | last post by:
Hi, I am new to this forum and have been troubled by this problem for the past few days. I would appreciate if someone would take a look and help me out. I am using vb express 2005 edition... ...
by: Anish G | last post by:
Hi All, I am getting the below given error while running my application in live server. In my local machine, its working fine. Please help me as it is very urgent for me. Exception from...
by: DontB3 | last post by:
Hi, I'm new in this forum, and i hope someone can help. I'm creating an automatic application that transfer a database from Access -> DBF -> Oracle. When My App try to execute Insert SQL...
by: LadyRed2 | last post by:
Hello, I am new to Excel and SQL server, so please be patient with me. I have written code with the help of others to update a table in a sql database from an excel form. The connection code...
by: pedalpete | last post by:
I'm getting a "missing ; before statement" error in javacript I'm surprised i wasn't able to find anything about this on this site. I am not sure why I'm getting this error, everything looks...
by: 362315 | last post by:
I am receiving the following error: Microsoft Visual Basic Run-Time error '2147467259 (80004005)': The microsoft jet database engine cannot open the file. It is already opened exclusively by...
by: EmilyA | last post by:
Hi! I'm not that good at programming, so I was wondering if anyone could tell me why I'm getting an unreachable statement error for lines 18, 35, 68, and 86. import java.util.ArrayList; ...
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.