Excel PivotField方向是错误的？ [英] Excel PivotField Orientation is wrong?

问题描述

我正在使用Excel 2010，并编写一个VBA函数来自动格式化数据透视表中的各种度量标准，因为它被用户更改（因此在添加/删除字段时格式保留）。我正在通过PivotFields集合来执行此操作。为了更改数据透视表需要在数据透视表中可见的格式，否则会抛出错误。所以我想我可以检查PivotField的Orientation属性，只处理那些设置为xlDataField的方向。

I'm using Excel 2010 and writing a VBA function to automatically format various metrics in a pivot table as it is changed by the user (so that formatting is preserved when they add/remove fields). I'm looping through the PivotFields collection to do this. In order to change the format the PivotField needs to be visible in the pivot table, otherwise it throws an error. So I figured I could just check the Orientation property of the PivotField and only handle those set to an Orientation of xlDataField.

但是，当我运行该函数时，所有数据字段正在显示一个xlHidden的方向，即使它们在数据透视表中可见。

However, when I run the function, all data fields are showing an Orientation of xlHidden, even if they are visible in the PivotTable.

我只是这样做一个简单的函数，这样

I'm just doing a simple function like this

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Dim pt As PivotField

    For Each pt In ActiveSheet.PivotTables("PivotTable1").PivotFields

        If VBA.InStr(1, pt.Caption, "COMP", vbTextCompare) And pt.Orientation = xlDataField Then
            pt.Function = xlAverage
            pt.NumberFormat = "0.00%"
        End If

    Next pt

End Sub

我甚至使用以下方式检查所有PivotFields：

I've even checking all PivotFields by using the following:

Debug.Print pt.Caption & " " & pt.Orientation

页面，行或列字段中没有的内容显示为隐藏。没有任何内容显示为数据字段。我错过了什么吗？没有关系我设置为数据字段，没有标记为xlDataField。我应该尝试另一种做法吗？我试过使用PivotFields.Hidden属性，但是我收到错误尝试访问它。

Anything not in the Page, Row or Column fields shows as Hidden. Nothing appears as a Data field. Am I missing something? It doesn't matter what I have set as Data fields, nothing is tagged as xlDataField. Should I try doing this another way? I've tried using the PivotFields.Hidden property but I get errors trying to access it.

推荐答案

而不是循环 PivotFields 你应该循环执行 xlDataField 方向的 DataFields 。

Instead of looping through PivotFields you should loop through DataFields which are those in xlDataField orientation.

如果您需要检查是否有任何before循环只是测试：

If you need to check if there is any before loop just test:

If ActiveSheet.PivotTables(1).DataFields.Count > 0 Then 

如果数据方向没有任何内容，则返回0

That return 0 if there is nothing in data orientation.

EDITED：
一件事 - 不要忘记关闭事件，因为您的过程会导致PT更改，再次触发该事件。所以，$ code> Application.EnableEvents = False 在开头和 = True 最后。

这篇关于Excel PivotField方向是错误的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！