Error 1004 vba excel when entering vlookup formula into cell

I have tried everything, including reading many questions in the forum about Error 1004 Application Defined or Object-Defined Error, but I can't find anything that is helping me fix the error. It writes the correct formula in the Immediate window.Here is the code. I've put where I get the error in the code.

The variable definitions are the following, and I've included the values in the watch window at the time of the Error 1004

Dim templateName As String       ' = "Company 1"   and yes this worksheet exists in my file
Dim servicesRow As Integer       ' = 22
Dim j as Integer                 ' = 1
Public firstProjectSOF as Range  ' firstProjectSOF.Row = 5
Dim columnTemp As Variant        ' columnTemp(0) = "A"
Public lastProjectSOF as Range   ' lastProjectSOF.Row = 65
Dim Rng as Range                 ' Rng.Column = 17



For j = 1 To numEmployees

    With Sheets("SOF").Range("E5", Range("E5").End(xlToRight))       'Finds the cell in SOF of that employee's last name
        Set Rng = .Find(What:=employees_lastName(j), _
                    LookIn:=xlValues, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
    End With
    'This is where the error 1004 occurs, on trying to write the VLOOKUP formula into the cell

    Sheets(templateName).Range(Cells(servicesRow + 1 + j, 5)).FormulaLocal = "=VLOOKUP($G$10,'Staff Output Final'!B" & firstProjectSOF.Row & ":" _
    & columnTemp(0) & lastProjectSOF.Row & "," & (Rng.Column - 1) & ",FALSE)"
Next

For info, if it is relevant, my computer is a Mac from Canada (English), but I recently edited the file on a PC computer in France (with OS in French). Just in case that was creating problems, I just tried creating a new excel file on my Mac in and copying and pasting the tabs from the old file into the new one, but I am still getting the Error 1004...

Thanks for any help! Much appreciated!