Formula with reference to variable workbook

[George Lee]

In the following macro I want to replace 'Portfolio_Position_Jul-10-2020.csv' with a variable Fname2
as this changes daily.
I have tried to replace with Fname2
ActiveCell.FormulaR1C1 = _
"=COUNTIF(Fname2!R3C2:R6C2,RC[-12])"
where Fname 2 equals "'Portfolio_Position_Jul-10-2020.csv'",'Portfolio_Position_Jul-10-2020.csv',"'[Portfolio_Position_Jul-10-2020.csv]'"
Could use some help as I'm fairly new to vba

Sub test1()
Dim Fname2 As String
Fname2 = "'Portfolio_Position_Jul-10-2020.csv'"

Range("n3").Activate

ActiveCell.FormulaR1C1 = _
"=COUNTIF('Portfolio_Position_Jul-10-2020.csv'!R3C2:R6C2,RC[-12])"
Range("N4").Activate
ActiveCell.FormulaR1C1 = _
"=COUNTIF('Portfolio_Position_Jul-10-2020.csv'!R3C2:R6C2,RC[-12])"
End Sub

 

[RoryA]

Welcome to the forum.

You need to concatenate the variable into the formula string:

ActiveCell.FormulaR1C1 = _
"=COUNTIF(" & FName2 & "!R3C2:R6C2,RC[-12])"

 

[George Lee]

Welcome to the forum.

You need to concatenate the variable into the formula string:

ActiveCell.FormulaR1C1 = _
"=COUNTIF(" & FName2 & "!R3C2:R6C2,RC[-12])"

Thank you very much