End Of Statement Error with VBA formula for Excel

[iceley11]

Hi,

I'm simply trying to put a formula in a range of cells in excel using VBA. I have the formula below:

Sub TimeSpentFormula()
    Range("SR_TimeSpent").Formula = _
      "=IF(OR(SR_DateClosed="CLOSED", _
      SR_DateClosed = "ON HOLD", _
      Resolution = "Open"), _
      "For Confirmation", _
      (SR_DateClosed-SR_DateOpened))"
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.

[zmbd]

iceley11:

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

[Rodney Roe]

Try this

Sub TimeSpentFormula()
      Range("SR_TimeSpent").Formula = _
       "=IF(OR(SR_DateClosed=""CLOSED""," & _
       "SR_DateClosed = ""ON HOLD""," & _
       "Resolution = ""Open"")," & _
       """For Confirmation""," & _
       "(SR_DateClosed-SR_DateOpened))"
End Sub
 

Personally i would just write it out instead of trying to separate into multiple lines.

[zmbd]

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:
http://bytes.com/topic/access/insigh...-when-use-them
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[Rodney Roe]

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.

[zmbd]

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.