问题

以下是翻译好的部分：

在下面的代码中，搜索透视表筛选器中特定名称的项目，如果找到，则按该名称进行筛选，然后更改列1为该名称，并调用另一个方法。这样做的目的是针对始终相同的6个名称执行操作。如何减少重复性？是否可以采用不同的方法？

<details>
<summary>英文:</summary>

code below searches specific name in items in pivot table filter and filters by that name if found. then changes column1 to that name, and calls another method. it does this for 6 names that always will be the same. how do I limit repetitiveness? should this be done any different?

For Each pvtitem In ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").PivotItems
If pvtitem.Name = "BUN" Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").CurrentPage _
= "BUN"
Column1 = "AA"
Call Brand(Range1, Column1)
Exit For
End If
Next

For Each pvtitem In ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").PivotItems
If pvtitem.Name = "CAX" Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").CurrentPage _
= "CAX"
Column1 = "AQ"
Call Brand(Range1, Column1)
Exit For
End If
Next

For Each pvtitem In ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").PivotItems
If pvtitem.Name = "CNF" Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").CurrentPage _
= "CNF"
Column1 = "BG"
Call Brand(Range1, Column1)
Exit For
End If
Next

For Each pvtitem In ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").PivotItems
If pvtitem.Name = "CVN" Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").CurrentPage _
= "CVN"
Column1 = "BW"
Call Brand(Range1, Column1)
Exit For
End If
Next

For Each pvtitem In ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").PivotItems
If pvtitem.Name = "GMN" Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").CurrentPage _
= "GMN"
Column1 = "DS"
Call Brand(Range1, Column1)
Exit For
End If
Next

For Each pvtitem In ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").PivotItems
If pvtitem.Name = "XCD" Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").CurrentPage _
= "XCD"
Column1 = "EY"
Call Brand(Range1, Column1)
Exit For
End If
Next

</details>


# 答案1
**得分**: 3

以下是代码部分的翻译：

```vba
这是使用`Select Case`的完美示例：

Sub tgr()

    Dim pvtitem As PivotItem
    Dim Range1 As Range
    Dim Column1 As String
    
    '设置 Range1 = <你的范围>
    '如果需要，你可以将其作为选择案例的一部分与 Column1 一起设置
    
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code")
        For Each pvtitem In .PivotItems
            Column1 = vbNullString
            Select Case UCase(pvtitem.Name)
                Case "BUN": Column1 = "AA"
                Case "CAX": Column1 = "AQ"
                Case "CNF": Column1 = "BG"
                Case "CVN": Column1 = "BW"
                Case "GMN": Column1 = "DS"
                Case "XCD": Column1 = "EY"
            End Select
            If Len(Column1) > 0 Then
                .CurrentPage = pvtitem.Name
                Call Brand(Range1, Column1)
            End If
        Next pvtitem
    End With

End Sub

请注意，代码部分已经翻译完毕。

Sub tgr()
    
    Dim pvtitem As PivotItem
    Dim Range1 As Range
    Dim Column1 As String
    
    'Set Range1 = <your range>
    'If necessary, you can set this as part of the select case along with Column1
    
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code")
        For Each pvtitem In .PivotItems
            Column1 = vbNullString
            Select Case UCase(pvtitem.Name)
                Case "BUN": Column1 = "AA"
                Case "CAX": Column1 = "AQ"
                Case "CNF": Column1 = "BG"
                Case "CVN": Column1 = "BW"
                Case "GMN": Column1 = "DS"
                Case "XCD": Column1 = "EY"
            End Select
            If Len(Column1) > 0 Then
                .CurrentPage = pvtitem.Name
                Call Brand(Range1, Column1)
            End If
        Next pvtitem
    End With
    
End Sub

答案2

得分: 1

workitems = Array("BUN-AA","CAX-AQ","CNF-BG","CVN-BW","GMN-BS","XCD-EY")
对于每个workitem
nm = 使用"-"分割(workitem)的第0个元素
clm = 使用"-"分割(workitem)的第1个元素
对于每个pvtitem 在ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").PivotItems中
如果 pvtitem.Name = nm
ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").CurrentPage = nm
Column1 = clm
调用 Brand(Range1, Column1)
退出循环
结束如果
结束循环
结束循环

稍后扩展很容易，只需添加更多的workitems。

workitems = Array("BUN-AA","CAX-AQ","CNF-BG","CVN-BW","GMN-BS","XCD-EY")
For Each workitem In workitems
    nm = Split(workitem, "-")(0)
    clm = Split(workitem, "-")(1)
    For Each pvtitem In ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").PivotItems
        If pvtitem.Name = nm Then
            ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").CurrentPage _
            = nm
            Column1 = clm
            Call Brand(Range1, Column1)
            Exit For
        End If
    Next
Next