470,842 Members | 1,321 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,842 developers. It's quick & easy.

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 1858
5,463 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,463 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,463 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

Post your reply

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

Similar topics

reply views Thread by Igor | last post: by
4 posts views Thread by Kevin R. | last post: by
6 posts views Thread by BaWork | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.