Pretty sure this can be accomplished but have tried all kinds of syntaxes and can't get it to work
I have the following formula in cell "A1":
='Z:\DataFiles[MyData.xlsm]Data'!$A$1
It's created with this line of code:
Sheets("MySheet").Range("A1") = "='Z:\DataFiles\[MyData.xlsm]Data'!$A$1"
In Cell "B1" on MySheet is "Z:\DataFiles"
In Cell "C1" on MySheet is "MyData.xlsm"
My question is how do I write the VBA to use the text in cells "B1" & "C1" to create the formula in "A1"?
This should work:
Sub setFormula()
With Sheets("MySheet")
.Range("A1").Formula = "='" & .Range("B1").Value & "\[" & .Range("C1").Value & "]Data'!$A$1"
End With
End Sub