遍历数组并更改OLAP数据透视表上的筛选器 [英] Loop Through Array and Change Filter on OLAP Pivot Table

问题描述

我对VBA还是很陌生，因此我正在寻找有关设置宏的帮助，该宏会将特定范围内的值添加到数组中，然后遍历该数组以更新数据透视表上的过滤器.但是，并非每个值都可以设置.例如，在100个值中仅设置了4个值，因此它只会循环遍历4次，并使用这4个值更新过滤器.我之前从未使用过for循环，因此对此的任何指导都将非常有帮助.

I'm pretty new to VBA so I'm looking for some help on setting up a macro that will add the values from a specific range to an array and then loop through that array to update the filters on a pivot table. However not every value might be set. An example of this would be that only 4 values are set out of a 100 so it would only loop through it 4 times and update the filter with those 4 values. I have never worked with for loops before so any guidance on this would be extremely helpful.

到目前为止，我的代码:

My code so far:

Sub Update_Filters()

    Dim PortfolioCodes As Variant
    PortfolioCodes = Sheets("Configuration Sheet").Range("C7:C45").Value

    Sheets("List").PivotTables("List").PivotFields( _
        "[Portfolio].[Portfolio Code].[Portfolio Code]").VisibleItemsList = Array( _
        "[Portfolio].[Portfolio Code].&[ABC1]", "[Portfolio].[Portfolio Code].&[ABC2]", _
        "[Portfolio].[Portfolio Code].&[XYZ1]", "[Portfolio].[Portfolio Code].&[XYZ2]")
End Sub

推荐答案

在这里，您的问题有两个可能的答案:

Here, the two possible answers to your question:

Option Explicit
Sub Update_Filters()

    'this code will filter one portfolio code at a time
    Dim LastRow As Long
    With ThisWorkbook.Sheets("Configuration Sheet")
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        Dim PortfolioCodes As Variant
        PortfolioCodes = .Range("C7:C" & LastRow).Value
    End With

    For i = LBound(PortfolioCodes) To UBound(PortfolioCodes)
        ThisWorkbook.Sheets("List").PivotTables("List").PivotFields( _
        "[Portfolio].[Portfolio Code].[Portfolio Code]").VisibleItemsList = _
            Array("[Portfolio].[Portfolio Code].&[" & PortfolioCodes(i) & "]")
        'some code
    Next i

End Sub
Sub Update_FiltersALL()

    'This code will filter all the portfolio codes in your range
    With ThisWorkbook.Sheets("Configuration Sheet")

        Dim LastRow As Long
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

        Dim i As Long
        i = 1

        ReDim PortfolioCodes(1 To LastRow - 6) As String
        Dim C As Range
        For Each C In .Range("C7:C" & LastRow)
            If Not IsEmpty(C) Then
                PortfolioCodes(i) = "[Portfolio].[Portfolio Code].&[" & C.Value & "]"
                i = i + 1
            End If
        Next C
    End With

    ThisWorkbook.Sheets("List").PivotTables("List").PivotFields( _
    "[Portfolio].[Portfolio Code].[Portfolio Code]").VisibleItemsList = PortfolioCodes

End Sub

这篇关于遍历数组并更改OLAP数据透视表上的筛选器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！