Excel VBA for pasting formula with a Path and a Variable

[pmacedge]

Hi all, I'm new in the forum as well as with VBA
I'm trying to program a macro so I can paste a specific formula into a cell including a path and a variable within it. Here's the code I'm trying:


Dim Path_VL As String
Dim Date_VL As String

Date_VL = Range("A3").Text

Path_VL = "=VLOOKUP(""09:35"";'C:\Users\pffma\Desktop\Folder1\[ " & Date_VL & ".xlsx]Sheet1'!$B:$F;2;FALSE)" 'This is the formula I would like to paste when running the macro...

Range("B4").Select
ActiveCell.FormulaR1C1 = Path_VL 'Here is the cell I want to paste the formula but it's debugging here

 

[rlv01]

Maybe this:

    Dim Path_VL As String
    Dim Date_VL As String
    Dim FilePath As String
    Dim SearchTerm As String
   
    SearchTerm = "09:35"
    Date_VL = Range("A3").Text
    FilePath = "C:\Users\pffma\Desktop\Folder1\" & Date_VL & ".xlsx"
   
   
    Path_VL = "=VLOOKUP(" & SearchTerm & ",'[" & FilePath & "]Sheet1'!$B:$F,2,FALSE)" 'This is the formula I would like to paste when running the macro...
    Range("B4").Formula = Path_VL

 

[pmacedge]

Maybe this:

    Dim Path_VL As String
    Dim Date_VL As String
    Dim FilePath As String
    Dim SearchTerm As String
  
    SearchTerm = "09:35"
    Date_VL = Range("A3").Text
    FilePath = "C:\Users\pffma\Desktop\Folder1\" & Date_VL & ".xlsx"
  
  
    Path_VL = "=VLOOKUP(" & SearchTerm & ",'[" & FilePath & "]Sheet1'!$B:$F,2,FALSE)" 'This is the formula I would like to paste when running the macro...
    Range("B4").Formula = Path_VL

Working beatifully )) Thank you so much you really helped