VBA Code To Insert Excel Formula In Cells Which Has Double Quote

In this post we will see how to insert excel formula in cells that have double quotes in them, there are number of instances where we are required to write formula(especially string related formula) with quotes(“”) and excel throws error, in this post we will see how to handle such cases.




In the example below we will see how to create an excel formula that has quotation marks embedded.

Step 1

Below is the sample data in which you can see column “Values” has some random data and we want to create a new column “New Values” in which we want to have the data from column “Values” but keep the cells as blank whenever we encounter “#NA”.

Excel table showing formula to insert double quote




Step 2

We will write a formula with double quotes(“”) in VBA and will insert the formula in excel cells.

We will create a macro in excel to do this, open VB editor from Developer tab in excel as shown in the figure.

Open developer tab to write VBA to delete shape on the basis of names

Insert a module in which we will write a small macro to insert the formula with double quote.

Insert a module in the VB editor

Now double click the newly created module to open that and paste the below code in the module.

Step 3

Sub Insert_Formula()

Worksheets(“Sheet1”).Range(“B2”).Formula = “=IF(Sheet1!A2=””#NA””,””””,Sheet1!A2)”

End Sub

This program will insert excel formula with double quotes in excel cell, now run it , Run the program by pressing F5 or by play button.

You can see that after the program is run, we have inserted the excel formula in cell B2 which serves our purpose.




Excel data showing double quote has been inserted in the formula

Hope this helped.

 

Share The Knowledge

Random Posts