title: Access vba to copy formulas in excel to last row
body: <p>I am running into a issue were the formula is getting copied down. however it is returning 0 even if the formula is True. when using it via Access vba. If i open excel macro workbook and run the same formula it returns the true value in the cells to the last row. could someone please look at my code and tell me what i am doing wrong. im Using MS Access 2016 and i have all the necessary reference tools selected. here is my code for Access vba.</p>
  <pre><code>Dim xlApp3 As Object
  Dim xlWBk3 Excel.Workbook
  Dim xlSh3, xlSh4, xlSh5, xlSh6 As Excel.Worksheet
  Dim strPath3 As String
  Dim xlCopy1 As Variant
      strPath3 = &quot;X:\Access_DataBases\Linked_Files\Funding_Integration_Workbook.xlsx&quot;
      Set xlApp3 = CreateObject(&quot;Excel.Application&quot;) 
      Set xlWB3 = xlApp3.Workbooks.Open(strPath3)
      Set xlSh3 = xlWB3.Sheets(&quot;Copied-FMEDDW-Data&quot;)
      Set xlSh4 = xlWB3.Sheets(&quot;Data Link to FMEDDW&quot;)
      Set xlSh5 = xlWB3.Sheets(&quot;Balance Pivot (Fund Level)&quot;)
      Set xlSh6 = xlWB3.Sheets(&quot;Funding Int Review Pivot&quot;)
      xlApp3.Visible = False
      xlApp3.ScreenUpdating = False
      xlApp3.DisplayAlerts = False
  xlSh4.Activate
  LastrowF = xlSh3.Range(&quot;A&quot; &amp; xlSh3.Rows.Count).End(xlUp).Row
  xlSh4.Range(&quot;AG2&quot;).Select
  xlCopy1 = xlSh4.Range(&quot;AG2&quot;).Formula
  xlSh4.Range(&quot;AG2&quot;).Formula = xlCopy1: xlSh4.Range(&quot;AG2:AG&quot; &amp; LastrowF).FillDown
  </code></pre>
  <p>When ran it copies the formula down to the last row however it returns all 0 (zeros).</p>
  <p>This formula is already sitting is excel cell AG2.
  Formula that is being copied:</p>
  <pre><code>=IF(J2=&quot;0100QP1D20&quot;,0,IF(F2=&quot;&quot;,&quot;&quot;,IF(AND(F2&lt;&gt;&quot;&quot;,F2=&quot;4610&quot;,D2&lt;&gt;&quot;GASD&quot;),N2,0)))
  </code></pre>
  <p>Now if i use the excel macro workbook this is the vba line i am using:</p>
  <pre><code>Sheets(&quot;Data Link to FMEDDW&quot;).Select
  Range(&quot;AG2&quot;).Select
      Lastrow = Worksheets(&quot;Copied-FMEDDW-Data&quot;).Range(&quot;A&quot; &amp; Rows.Count).End(xlUp).Row
      Selection.Copy
  Range(&quot;AG2:AG&quot; &amp; Lastrow).PasteSpecial xlPasteFormulas
  </code></pre>
  <p>This works perfect when ran from excel macro workbook. it returns the value (Dollar amounts).</p>
  <p>A little back story with this. The excel is being updated from a report of around 5k records or more and grows daily. I have added column AG to return the value of the said formula. any help is greatly appreciated. Thanks in andvance.</p>