Access vba to copy formulas in excel to last row

Question

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.

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 = "X:\Access_DataBases\Linked_Files\Funding_Integration_Workbook.xlsx"
    Set xlApp3 = CreateObject("Excel.Application") 
    Set xlWB3 = xlApp3.Workbooks.Open(strPath3)
    Set xlSh3 = xlWB3.Sheets("Copied-FMEDDW-Data")
    Set xlSh4 = xlWB3.Sheets("Data Link to FMEDDW")
    Set xlSh5 = xlWB3.Sheets("Balance Pivot (Fund Level)")
    Set xlSh6 = xlWB3.Sheets("Funding Int Review Pivot")
    xlApp3.Visible = False
    xlApp3.ScreenUpdating = False
    xlApp3.DisplayAlerts = False
xlSh4.Activate
LastrowF = xlSh3.Range("A" & xlSh3.Rows.Count).End(xlUp).Row
xlSh4.Range("AG2").Select
xlCopy1 = xlSh4.Range("AG2").Formula
xlSh4.Range("AG2").Formula = xlCopy1: xlSh4.Range("AG2:AG" & LastrowF).FillDown

When ran it copies the formula down to the last row however it returns all 0 (zeros).

This formula is already sitting is excel cell AG2. Formula that is being copied:

=IF(J2="0100QP1D20",0,IF(F2="","",IF(AND(F2<>"",F2="4610",D2<>"GASD"),N2,0)))

Now if i use the excel macro workbook this is the vba line i am using:

Sheets("Data Link to FMEDDW").Select
Range("AG2").Select
    Lastrow = Worksheets("Copied-FMEDDW-Data").Range("A" & Rows.Count).End(xlUp).Row
    Selection.Copy
Range("AG2:AG" & Lastrow).PasteSpecial xlPasteFormulas

This works perfect when ran from excel macro workbook. it returns the value (Dollar amounts).

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.

Fabricio Fonseca Fabricio Fonseca · Accepted Answer · 2020-07-06T16:34:41

The error may be in the F2="4610". This is a string and 4610 is a number. Try something like Value(F2)=4610 as number or TEXT(F2,0) = "4610"

Access vba to copy formulas in excel to last row

1 Answers