Excel (2007) VBA - .Formula with quotes in it

excel excel-2007 vba
GeoffDS picture GeoffDS · Apr 13, 2012 · Viewed 81.1k times · Source

I tried putting the following code into a program yesterday. VBA called an error. I assume it is because of the double quotes inside the formula. I googled and all results I found just gave the basic of putting formulas in, but none explained how to get around quotes inside.

(there was a With statement before this, Pivot is a worksheet name)

.Range("A2").Formula = "=IF(Pivot!A5="",A1,Pivot!A5)"

Any help is much appreciated. Thanks!

Answer

Siddharth Rout picture Siddharth Rout · Apr 13, 2012

Whenever in doubt, record a macro if it allows :)

Try this

.Range("A2").Formula = "=IF(Pivot!A5="""",A1,Pivot!A5)"

Related questions

How to do a "Save As" in vba code, saving my current Excel workbook with datestamp?

I have an Excel Workbook that on form button click I want to save a copy of the workbook with the filename being the current date. I keep trying the the following ActiveWorkbook.SaveAs ("\\filePath\FormFlow To MSExcel\" & Left(…

Read More
Closing Excel Application using VBA

I have used the following without success. The active workbook closes, indeed, but the excel window remains open. Application.ActiveWindow.Close SaveChanges:=False ActiveWorkbook.Close SaveChanges:=False Which is the command that terminates the application? EDIT To say a little …

Read More
Excel Date Conversion from yyyymmdd to mm/dd/yyyy

I have been searching for about an hour on how to do this in Excel. I have an Excel file that was created from an old system and I am pulling information from a SQL Server Database, I will be …

Read More