How to include double quotation marks in a formula in vba

[dpaton05]

I am trying to insert a formula using vba but it gives me a syntax error, what is wrong with this line of code?

.range("C11").formula = "=IF(A11="","",IF(COUNTIF(Sheet2!$G$87:$DO$97,A11),"Public Holiday",IF(WEEKDAY(A11)=1,"Sun",IF(WEEKDAY(A11)=7,"Sat","Mon-Fri"))))"

 

[MARK858]

You double all your quotes.

 

[dpaton05]

I tried to double all the quotes and I still got a syntax error.

 .range("C11").formula = ""=IF(A11="""","""",IF(COUNTIF(Sheet2!$G$87:$DO$97,A11),""Public Holiday"",IF(WEEKDAY(A11)=1,""Sun"",IF(WEEKDAY(A11)=7,""Sat"",""Mon-Fri""))))""

 

[dpaton05]

I just found it, I doubled the quote marks surrounding the formula, I removed them and it worked, thanks for that.

 

[dpaton05]

Got another error with entering a formula mark. I get the error message, Application defined or object defined error, with trying to insert the following formula

.Range("M11").Formula = "=IF([@[Wait Time/Hrs]]<3,3,[@[Wait Time/Hrs]])"

The names are all table column headers.

 

[Rick Rothstein]

Got another error with entering a formula mark. I get the error message, Application defined or object defined error, with trying to insert the following formula

.Range("M11").Formula = "=IF([@[Wait Time/Hrs[B][COLOR="#FF0000"]]][/COLOR][/B]<3,3,[@[Wait Time/Hrs[B][COLOR="#FF0000"]]][/COLOR][/B])"

The names are all table column headers.

Why the double closing square brackets?

 

[Fluff]

Maybe
.Range("M11").Formula = "=IF([@Wait Time/Hrs]<3,3,[@Wait Time/Hrs])"

or even
.Range("M11").Formula = "=MAX(3,[@Wait Time/Hrs])"

 

[RoryA]

The formula syntax is OK, assuming that M11 is actually part of the table? If not, you would get an error as the formula is missing a table name.