vba - 宏在空白时不显示详细信息

Question

我有一个包含两个行字段和一个值列的数据透视表宏。我试图弄清楚我如何才能正确If Statement检测“调整金额”中是否没有数字，详细信息将在名为“分类帐”的列中关闭。下面是我的代码：

 Sub MakeAPivotTable()

Dim pt As pivotTable 'Creates pivot table
Dim pc As PivotCache 'Pivot Cache
Dim pf As PivotField 'Pivot Field
Dim pi As PivotItem
Dim PRange As Range 'Source data range
Dim lastRow As Long 'Last Row of Report even if it changes
Dim lastCol As Long 'Last Column of Report even if it changes
Dim DSheet As Worksheet 'Claiming "Report" sheet as data sheet
Dim PSheet As Worksheet 'Claiming what is pivot sheet for this macro
Dim ptType As String
Dim ADJ As Range


'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Metrics").Delete
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Metrics"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Metrics")
Set DSheet = Worksheets("Report")
    With ActiveWorkbook.Sheets("Metrics").Tab
        .Color = 4555
        .TintAndShade = 0
    End With

'Defining Data Range. Taking the first cell of the data table and select uptp the last row and then up to the last column
    lastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
    lastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Set PRange = DSheet.Cells(1, 1).Resize(lastRow, lastCol)

'Create pivot cache. Defines Pivot Cache by using Data source and defines the cell address in the new worksheet to insert pivot table
    Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), TableName:="Accrual")

'Insert Pivot Table
    Set pt = pc.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Accrual")

'Putting Row Fields in
    With ActiveSheet.PivotTables("Accrual").PivotFields("Ledger Name")
        .Orientation = xlRowField
        .Position = 1
    End With

    With ActiveSheet.PivotTables("Accrual").PivotFields("CC")
        .Orientation = xlRowField
        .Position = 2
    End With
        ActiveSheet.PivotTables("Accrual").CompactLayoutRowHeader = "Ledger"


'Putting Values Field in
 ActiveSheet.PivotTables("Accrual").AddDataField ActiveSheet.PivotTables( _
        "Accrual").PivotFields("Adjustment Amt"), "Sum of Adjustment Amt", _
        xlSum
  ActiveSheet.PivotTables("Accrual").DataPivotField.PivotItems( _
        "Sum of Adjustment Amt").Caption = "Adjustment Amount"

    With ActiveSheet.PivotTables("Accrual").PivotFields("Adjustment Amount")
        .NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    End With

这就是我认为可行的方法：

For Each ADJ In Worksheets("Metrics").Range("C3:C100")

    If ADJ = "" Then

         ActiveSheet.PivotTables("Accrual").PivotFields("Ledger Name").Selection.ShowDetail = False
    End If
Next

标签： vbaexcel