时间戳

首页 > 解决方案 > 如何使这不重复？

excel - 如何使这不重复？

问题描述

下面的代码在数据透视表过滤器的项目中搜索特定名称，如果找到则按该名称过滤。然后将 column1 更改为该名称，并调用另一个方法。它对 6 个始终相同的名称执行此操作。如何限制重复性？应该这样做吗？


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

标签： excelvba

解决方案

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

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

推荐阅读