Pass File Location String into VBA Formula

Question

Anyone know what's wrong with this? Simply put its told to open two spreadsheets from a file location given 2 different cells. Then im trying to put the file location in a formula so it counts how many times the reference on the 2nd workbook, is on the 1st work book.

Every time i run it crashes when it gets to the COUNTIF formula part at the bottom and i can't work out where im going wrong. I haven't done macros in like 10 years. lol

For reference OldPath string is C:\Users\Nobbsy\Downloads\Copy of January 2024 Alterations.xlsx

Sub RRQP()

'RRQP Macro
'This macro will open a workbook

Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Dim FullPath As String
Dim OldPath As String
FullPath = Range("G6")
OldPath = Range("G4")
Workbooks.Open (OldPath)
Workbooks.Open (FullPath)
ActiveSheet.Name = "Transaction Report"
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True
Range("D2").Select
' formula time

Dim last_row As Long

last_row = Cells(Rows.Count, 2).End(xlUp).Row

ActiveCell.Formula = "=COUNTIF(OldPath,Transaction Report'!$A$1:$A$10000,A2)"

ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":D" & last_row)

End Sub

Answer 1

You need to adjust OldPath so it only is the workbook name, not the full path. It also helps to declare the workbooks you're going to use and the worksheet the range is in.

Sub RRQP()
    'RRQP Macro
    'This macro will open a workbook
    
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    Dim FullPath As String, OldPath As String
    Dim wbO As Workbook, wbF As Workbook, ws As Worksheet
    FullPath = Range("G6")
    OldPath = Range("G4")
    Set wbO = Workbooks.Open(OldPath)
    Set wbF = Workbooks.Open(FullPath)
    Set ws = wbF.ActiveSheet
    ws.Name = "Transaction Report"
    Application.DisplayAlerts = True
    Application.AskToUpdateLinks = True
    'Range("D2").Select 'Don't use Select
    ' formula time
    
    Dim last_row As Long, rng As Range
    
    last_row = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
    Set rng = ws.Range("D2:D" & last_row)
    OldPath = wbO.Name
    rng.Formula = "=COUNTIF('[" & OldPath & "]Transaction Report'!$A$1:$A$10000,A2)"
    'rng.AutoFill Destination:=Range(ActiveCell.Address & ":D" & last_row)
    'Unnecessary since we're giving the formula to the entire range from D2 to last row
End Sub

Also check out how to avoid Select/Activate for more examples on that part.
Let me know if I misunderstood your intention with what goes where :)