VBA - Using a String value in formula

[CeReaLK]

Hello,
I am prety new to VBA and i was wondering if its possible to use a String as part of a formula like the example below.

Sub Test()

Dim IT_FR_IT_Rate As String
IT_FR_IT_Rate = Range("A1").Value - A1 containts the excel file name Filename.xslx.
Range("A2").Select
ActiveCell.Formula = _
"=INDEX('C:\Path\path2\""& IT_FR_IT_Rate &""'!Target_EUR_Difd,1,0)"
End Sub

If not, how could i make this work. The file name should be stored in a cell, so that it can be easily changed.
Note that the destination excel file is closed.

Thank you in advance.

 

[Fluff]

Hi & welcome to MrExcel.
What is Target_EUR_Difd ?

 

[CeReaLK]

Hello,
its a named range located in the target file..
The above is just a part of the whole formula. The original formula contains more Indexes/matches to the same target file.
Thats why i used only a part.

 

[Fluff]

In that case try

Dim IT_FR_IT_Rate As String
IT_FR_IT_Rate = Range("A1").Value
Range("A2").Formula = _
   "=INDEX('C:\Path\Path2\" & IT_FR_IT_Rate & "'!Target_EUR_Difd,1,0)"

 

[CeReaLK]

Worked like a charm.

Thanks alot!

 

[Fluff]

You're welcome & thanks for the feedback.