VBA - Insert formula that references closed workbook, Run-time error '1004':

Question

I'm trying to insert a formula into a cell using VBA, the formula is an index and match to a closed workbook. I'm getting a Run-time error '1004': Application-defined or object-defined error:

Sub forecastData()

Worksheets("Mon").Range("R17").Formula = "=INDEX(""'""L:\ECommerce\Trading\Web Analytics\Reporting\KPI\[Ecom KPI.xlsm]Forecast""'""!$B$6:$B$2927,MATCH(""'""Update Data""'""!$E$2,""'""L:\ECommerce\Trading\Web Analytics\Reporting\KPI\[Ecom KPI.xlsm]Forecast""'""!$A$6:$A$2927,0))"

End Sub

Can anyone see where I've gone wrong?

Cheers

megatron77 megatron77 · Accepted Answer · 2016-03-31T10:31:12

Nevermind, fixed it using Chr(39):

Worksheets("Mon").Range("R17").Formula = "=INDEX(" + Chr(39) + "L:\ECommerce\Trading\Web Analytics\Reporting\KPI\[Ecom KPI.xlsm]Forecast" + Chr(39) + "!$B$6:$B$2927,MATCH(" + Chr(39) + "Update Data" + Chr(39) + "!$E$2," + Chr(39) + "L:\ECommerce\Trading\Web Analytics\Reporting\KPI\[Ecom KPI.xlsm]Forecast" + Chr(39) + "!$A$6:$A$2927,0))"

VBA - Insert formula that references closed workbook, Run-time error '1004':

1 Answers