By user793468


2012-01-26 20:18:31 8 Comments

I want to insert an if statement in a cell through vba which includes double quotes.

Here is my code:

Worksheets("Sheet1").Range("A1").Value = "=IF(Sheet1!B1=0,"",Sheet1!B1)"

Due to double quotes I am having issues with inserting the string. How do I handle double quotes?

5 comments

@Sharunas Bielskis 2016-07-19 11:08:58

All double quotes inside double quotes which suround the string must be changed doubled. As example I had one of json file strings : "delivery": "Standard", In Vba Editor I changed it into """delivery"": ""Standard""," and everythig works correctly. If you have to insert a lot of similar strings, my proposal first, insert them all between "" , then with VBA editor replace " inside into "". If you will do mistake, VBA editor shows this line in red and you will correct this error.

@shrivallabha.redij 2017-10-20 04:53:16

I have written a small routine which copies formula from a cell to clipboard which one can easily paste in Visual Basic Editor.

    Public Sub CopyExcelFormulaInVBAFormat()
        Dim strFormula As String
        Dim objDataObj As Object

        '\Check that single cell is selected!
       If Selection.Cells.Count > 1 Then
            MsgBox "Select single cell only!", vbCritical
            Exit Sub
        End If

        'Check if we are not on a blank cell!
       If Len(ActiveCell.Formula) = 0 Then
            MsgBox "No Formula To Copy!", vbCritical
            Exit Sub
        End If

        'Add quotes as required in VBE
       strFormula = Chr(34) & Replace(ActiveCell.Formula, Chr(34), Chr(34) & Chr(34)) & Chr(34)

        'This is ClsID of MSFORMS Data Object
       Set objDataObj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        objDataObj.SetText strFormula, 1
        objDataObj.PutInClipboard
        MsgBox "VBA Format formula copied to Clipboard!", vbInformation

        Set objDataObj = Nothing

    End Sub

It is originally posted on Chandoo.org forums' Vault Section.

@D Zeller 2017-02-24 19:11:19

Another work-around is to construct a string with a temporary substitute character. Then you can use REPLACE to change each temp character to the double quote. I use tilde as the temporary substitute character.

Here is an example from a project I have been working on. This is a little utility routine to repair a very complicated formula if/when the cell gets stepped on accidentally. It is a difficult formula to enter into a cell, but this little utility fixes it instantly.

Sub RepairFormula()
Dim FormulaString As String

FormulaString = "=MID(CELL(~filename~,$A$1),FIND(~[~,CELL(~filename~,$A$1))+1,FIND(~]~, CELL(~filename~,$A$1))-FIND(~[~,CELL(~filename~,$A$1))-1)"
FormulaString = Replace(FormulaString, Chr(126), Chr(34)) 'this replaces every instance of the tilde with a double quote.
Range("WorkbookFileName").Formula = FormulaString

This is really just a simple programming trick, but it makes entering the formula in your VBA code pretty easy.

@Jobin Lopez 2016-01-21 04:14:55

I prefer the answer of tabSF . implementing the same to your answer. here below is my approach

Worksheets("Sheet1").Range("A1").Value = "=IF(Sheet1!A1=0," & CHR(34) & CHR(34) & ",Sheet1!A1)"

@Brain2000 2012-01-26 20:21:27

I find the easiest way is to double up on the quotes to handle a quote.

Worksheets("Sheet1").Range("A1").Formula = "IF(Sheet1!A1=0,"""",Sheet1!A1)" 

Some people like to use CHR(34)*:

Worksheets("Sheet1").Range("A1").Formula = "IF(Sheet1!A1=0," & CHR(34) & CHR(34) & ",Sheet1!A1)" 

*Note: CHAR() is used as an Excel cell formula, e.g. writing "=CHAR(34)" in a cell, but for VBA code you use the CHR() function.

@gicalle 2014-09-11 09:34:14

I prefer creating a global variable: Public Const vbDoubleQuote As String = """" 'represents 1 double quote (") Public Const vbSingleQuote As String = "'" 'represents 1 single quote (') and using it like so: Shell "explorer.exe " & vbDoubleQuote & sPath & vbDoubleQuote, vbNormalFocus

@rolinger 2016-06-02 08:05:58

@gicalle 's answer is really neat, it takes all the confusion of 'how many single and double quotes' out to one definition where it can be documented

@rolls 2016-12-14 03:18:32

I use lots of global defines in all my VBA projects (when I don't have a choice in using something other than VBA!!) that define all the common things like carriage return, double quotes etc.

Related Questions

Sponsored Content

18 Answered Questions

[SOLVED] How to add double quotes to a string that is inside a variable?

1 Answered Questions

[SOLVED] Qutation Marks within VBA for IF statement

2 Answered Questions

[SOLVED] Inserting double quotes string within formula within VBA (Excel

  • 2018-06-08 21:28:22
  • Oday Salim
  • 739 View
  • 1 Score
  • 2 Answer
  • Tags:   excel vba excel-vba

0 Answered Questions

VBA not interpreting a string containing dual double-quote

  • 2017-08-04 11:40:11
  • Terek Li
  • 138 View
  • 0 Score
  • 0 Answer
  • Tags:   sql vba

1 Answered Questions

[SOLVED] Run VBA Function in selected sheets

  • 2017-06-11 16:01:15
  • andziunia27
  • 218 View
  • -1 Score
  • 1 Answer
  • Tags:   excel-vba vba excel

2 Answered Questions

[SOLVED] Placing Double Quotes Within a String in VBA

3 Answered Questions

[SOLVED] How can I find quoted text in a string?

3 Answered Questions

[SOLVED] VBA Search all sheets for double clicked cell value

1 Answered Questions

[SOLVED] Excel VBA: How to duplicate double-click/cell edit action in vba code

  • 2015-03-14 23:46:09
  • Aaron
  • 839 View
  • 0 Score
  • 1 Answer
  • Tags:   excel vba

1 Answered Questions

[SOLVED] Manage quotes inside string VBA

Sponsored Content