Search code examples
excelvba

Formula not autofilling in excel table (VBA)

I have some code that adds some formulas to the top row of a ListObject. Previously the formula would autofill into the whole ListColumn. I recently added a column to the ListObject and suddenly the formula no longer autofills. I have confirmed that the "automatic calculations" is turn on, that the formulas are accurate, and that the code is really being added to the top rows. When i unhide the rows, a tooltip shows up and allows me to select "autofill."

Any ideas that I need to look into? Is it better to build an autofill into the vba so this doesn't happen unexpectedly when a user is using the workbook?

Private Sub Add_FormulasToSalesAfterClassify()
    
        'Set worksheet
        Dim importWS As Worksheet
        Dim table As ListObject
        Dim lastRow As Long
        
        lastRow = Range("T:T").SpecialCells(xlCellTypeLastCell).Row
        
        Set importWS = Worksheets("Sales Data Monthly")
        Set table = importWS.ListObjects("Table_SalesMonthly")
        
        'Add formulas
        'Product
        importWS.Range("T3").Formula = "=IF(\[@\[Select Decision\]\]=""Classify"",\[@\[Select Product\]\],IF(\[@Location\]=""Non-Atrium"",""Non Atrium"",INDEX(Table_Material,MATCH(\[@\[Material Number\]\],Table_Material\[Material Number\],0),MATCH(Table_SalesMonthly\[\[#Headers\],\[Product\]\],'Material No. to Product'!Print_Titles,0))))"
        'Product Group
        importWS.Range("U3").Formula = "=INDEX(Table_Group,MATCH(\[@Product\],Table_Group\[Product\],0),MATCH(Table_SalesMonthly\[\[#Headers\],\[Product Group\]\],'Product to Product Group'!Print_Titles,0))"
        'Conversion
        importWS.Range("V3").Formula = "=IF(\[@\[Select Decision\]\]=""Classify"",\[@\[Enter Conversion\]\],INDEX(Table_Material,MATCH(\[@\[Material Number\]\],Table_Material\[Material Number\],0),MATCH(Table_SalesMonthly\[\[#Headers\],\[Conversion Rate\]\],'Material No. to Product'!Print_Titles,0)))"
        'Sold
        importWS.Range("W3").Formula = "=IFERROR(\[@\[Sold Units\]\]*\[@\[Conversion Rate\]\],""Error"")"
        
        'Application.Run "Protect"
    
    End Sub

enter image description here

Solution

  • Thanks for your help. I ended up writing a for loop for the visible cells and that solved my problem.

    Private Sub Add_FormulasToSalesAfterClassify()
    
    Application.AutoCorrect.AutoFillFormulasInLists = True

    'Set worksheet
    Dim importWS As Worksheet
    Dim table As ListObject
    Dim cell As Range
    Dim columnProduct, columnGroup, columnRate, columnSold As Range
    
    Set importWS = Worksheets("Sales Data Monthly")
    Set table = importWS.ListObjects("Table_SalesMonthly")
    
    'Product column
    Set columnProduct = table.ListColumns(20).DataBodyRange.SpecialCells(xlCellTypeVisible)
    For Each cell In columnProduct
        With columnProduct
        .Formula = "=IF([@[Select Decision]]=""Classify"",[@[Select Product]],IF([@Location]=""Non-Atrium"",""Non Atrium"",INDEX(Table_Material,MATCH([@[Material Number]],Table_Material[Material Number],0),MATCH(Table_SalesMonthly[[#Headers],[Product]],'Material No. to Product'!Print_Titles,0))))"
        End With
    Next

    'Product group
    Set columnGroup = table.ListColumns(21).DataBodyRange.SpecialCells(xlCellTypeVisible)
    For Each cell In columnGroup
        With columnGroup
        .Formula = "=INDEX(Table_Group,MATCH([@Product],Table_Group[Product],0),MATCH(Table_SalesMonthly[[#Headers],[Product Group]],'Product to Product Group'!Print_Titles,0))"
        End With
    Next

    'Product rate
    Set columnRate = table.ListColumns(22).DataBodyRange.SpecialCells(xlCellTypeVisible)
    For Each cell In columnRate
        With columnRate
        .Formula = "=IF([@[Select Decision]]=""Classify"",[@[Enter Conversion]],INDEX(Table_Material,MATCH([@[Material Number]],Table_Material[Material Number],0),MATCH(Table_SalesMonthly[[#Headers],[Conversion Rate]],'Material No. to Product'!Print_Titles,0)))"
        End With
    Next

    'Product column
    Set columnSold = table.ListColumns(23).DataBodyRange.SpecialCells(xlCellTypeVisible)
    For Each cell In columnSold
        With columnSold
        .Formula = "=IFERROR([@[Sold Units]]*[@[Conversion Rate]],""Error"")"
    End With
    Next



End Sub