Dynamic file path

[srdr16]

I have an issue with dynamic file names/paths within my spreadsheet vba.
I have a master file that does a countif against a file and path and worksheet that changes months and file names
I have set up Dim filepath, which picks up the file path, file name and worksheet from a worksheet on my spreadsheet
The vba runs to a point and gives me A:\mypath\mydata\[myfile.xlsx]MyWorksheet
This shows in the debug.print so I know it works
My original formula (that has a fixed location is)
"=COUNTIFS('A:\mypath\mydata\[my file.xlsx]MyWorksheet'!C7,RC11,''A:\mypath\mydata\[my file.xlsx]MyWorksheet'!C14,R2C)"
This works fine, however I can't get the variable location formula to work.

I've tried this
"=Countifs(""" & filepath & """ & ""!C7,RC11," & """ & filepath & """ & "!C14,R2C)"""""

As I've said, my original formula works fine, the file location appears on the debug.print the same as the original macro so I must just be formatting the new macro incorrectly.
Any help would be appreciated.

 

[sofas]

Try this

Dim filepath As String
filepath = "A:\mypath\mydata\[myfile.xlsx]MyWorksheet"

Dim formula As String
formula = "=COUNTIFS('" & filepath & "'!C7,RC11,'" & filepath & "'!C14,R2C)"
Range("A1").Formula = formula