title: Error 1004 vba excel when entering vlookup formula into cell
body: <p>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.</p>
  
  <p>The variable definitions are the following, and I've included the values in the watch window at the time of the Error 1004</p>
  
  <pre><code>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" &amp; firstProjectSOF.Row &amp; ":" _
      &amp; columnTemp(0) &amp; lastProjectSOF.Row &amp; "," &amp; (Rng.Column - 1) &amp; ",FALSE)"
  Next
  </code></pre>
  
  <p>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...</p>
  
  <p>Thanks for any help! Much appreciated!</p>