Search code examples
excelvbaexcel-formula

Creating a formula in vba using cell values

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"?

Solution

  • This should work:

    Sub setFormula()
    With Sheets("MySheet")
        .Range("A1").Formula = "='" & .Range("B1").Value & "\[" & .Range("C1").Value & "]Data'!$A$1"
    End With
End Sub