How to include double quotation marks in a formula in vba

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,352
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am trying to insert a formula using vba but it gives me a syntax error, what is wrong with this line of code?
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"))))"
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
I tried to double all the quotes and I still got a syntax error.
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""))))""
 
Upvote 0
I just found it, I doubled the quote marks surrounding the formula, I removed them and it worked, thanks for that.
 
Upvote 0
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

Code:
.Range("M11").Formula = "=IF([@[Wait Time/Hrs]]<3,3,[@[Wait Time/Hrs]])"
The names are all table column headers.
 
Upvote 0
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

Code:
.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?
 
Upvote 0
Maybe
.Range("M11").Formula = "=IF([@Wait Time/Hrs]<3,3,[@Wait Time/Hrs])"

or even
.Range("M11").Formula = "=MAX(3,[@Wait Time/Hrs])"
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top