Convert Vlookup Formula to VBA Code and Select the Most Current Date from Slicer

Chendo · Apr 25th 2023

I have vlookup formula that looks up the value in D8, active field name ‘ProductID ‘ in the pivot table named ‘pvt_Production_Summary’ and the returned value divides the value in E8 active field ‘Sum of Cases’. I'm working on the below code convert this formula into VBA. However, I keep getting a Run-Time error message ‘1004’. Application-defined or object-defined error. on ‘For Each si In sl.SlicerItems’

I know that the SlicerCaches ‘Slicer_ProductionDate’ is associated to the pivot table ‘pvt_Production_Summary. I would greatly appreciate your time and help.

Code

Sub Update_Formula()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim sl As SlicerCache
    Dim si As SlicerItem
    Dim lastrow As Long
    Dim rng As Range
    Dim formulaString As String

    Set ws = ActiveWorkbook.Sheets(1) 'Replace 1 with the index number of the worksheet containing the pivot table
    Set pt = ws.PivotTables("pvt_Production_Summary") 'Replace "pvt_Production_Summary" with the name of the pivot table
    
    'Check the SlicerCaches collection
    Dim sc As SlicerCache
    For Each sc In ActiveWorkbook.SlicerCaches
        Debug.Print sc.Name
    Next sc
    
    Set sl = ActiveWorkbook.SlicerCaches("Slicer_ProductionDate") 'Replace "Slicer_ProductionDate" with the name of the slicer cache
    lastrow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row 'Replace "D" with the column letter containing the D8 reference

    For Each si In sl.SlicerItems
        si.Visible = False 'Hide all items in the slicer
        Set rng = ws.Range("D8:D" & lastrow) 'Set range of cells to be evaluated in the formula
        rng.AutoFilter Field:=1, Criteria1:=si.Value 'Filter the range based on the current slicer item
        formulaString = "=IFERROR(E8/VLOOKUP(D8,tbl_Cases_Per_Pallet,3,FALSE),""0"")" 'Replace "tbl_Cases_Per_Pallet" with the name of the table being used in the VLOOKUP formula
        rng.Offset(0, 1).Formula = formulaString 'Apply the formula to the visible cells in the adjacent column
        si.Visible = True 'Show the current slicer item again
    Next si

    pt.RefreshTable 'Refresh the pivot table
End Sub

Display More

**Carim** · Apr 26th 2023

Hi and Welcome to the Forum

Your question is not extremely clear :

1. If you need to update Slicers with a Macro :

Update Excel Slicers with Macro

www.contextures.com

2. If you need to convert a worksheet formula into VBA, you should consider Evaluate

VBA Evaluate Function - Automate Excel

In this ArticleEvaluate SyntaxReturn a Result to Excel with EvaluateFormat a Cell with EvaluateShorten Formulas with the Evaluate Function This article will…

www.automateexcel.com

Hope this will help

Chendo · May 1st 2023

Carim,

Thank you for looking into this thread. As you can see from the image below, I am using a vlookup to search for product ID in the tbl_Cases_Per_Pallet, and the returned value divides the value in E8 active field ‘Sum of Cases’. One instead of using the vlookup function, I want to use the above VBA code to accomplish this task as well as select the most current date from the slicer, which in this case would be 4/27/23. I would prefer that the VBA lookup code to return the corresponding value when the pivot table is refreshed. I would greatly appreciate your help and time.

**Carim** · May 1st 2023

Hello,

Without a sample file, it is quite difficult to guess all your constraints and how the Pivot Table operates based on which data ...

As a general answer, for a simple and easy workaround, you can add a Column to your database which provides the foundation for your Pivot Table ...

Hope this will help

Convert Vlookup Formula to VBA Code and Select the Most Current Date from Slicer

Participate now!

Share