PivotItems Are Wrong when Calculated Field Is Present

I have a procedure where I loop through a pivot table and create a general journal entry to be imported. I used the same procedure to create another journal entry, but with one change. For this new journal entry, I wanted to round all of the amounts to the nearest $10. They were estimates and I don’t like to post estimates to the penny because it implies a precision that just isn’t there.

To accomplish this seemingly simple task, I created a calculated field and adjusted the procedure to pull from that field. There was just one problem: my debits didn’t equal my credits! I know, I gasped too.

It turns out that looping through the pivot table with For Each pi In pf.PivotItems was starting on the row below the first pivot item and ending on the row below the last pivot item. It was offset one row.

I was puzzled for quite a while. The old procedure worked fine. Then it dawned on me that I had added a calculated field. When I removed the calculated field, it worked as expected. So I modified the procedure to do the rounding in VBA.

Next, I wanted to see if this was a fluke. I generated some sample data, namely First Name, Last Name, and City. Then I created a pivot table with City in the Row Labels section and Count of Last in the Value section. Finally, I created a calculated field expertly named Field1.

The calculated field is set to zero. It’s not really calculating anything. I added that to the Values section.

Now to test. I wrote some code to demonstrate that the PivotItems were pointing to the wrong cells. As part of my code, I wanted to remove the calculated field. I kept getting errors trying to remove the calculated field so I recorded a macro to see how it’s supposed to be done.

ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Field1").Orientation = xlHidden

Yep, that’s what I was trying to do. Only that doesn’t work. So I found a work around. I love finding bugs when I’m investigating other bugs.

Here’s the code:

Sub TestPivotItems()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = ActiveCell.PivotTable
Set pf = pt.PivotFields("City")
Set pi = pf.PivotItems(1)

Debug.Print pi.LabelRange.Address, pi.LabelRange.Value
Debug.Print ActiveSheet.Cells.Find(pi.Value).Address, pi.Value
Debug.Print String(40, "-")

pt.PivotFields("Sum of Field1").DataRange.Cells(1).PivotItem.Visible = False

Debug.Print pi.LabelRange.Address, pi.LabelRange.Value
Debug.Print ActiveSheet.Cells.Find(pi.Value).Address, pi.Value

End Sub

And here’s the result:

Above the dashed line is what happens when calculated field is present. The PivotItem, which is in A4, shows A5 as its LabelRange. When I search for its Value, however, I get A4. When I removed the calculated field, it all works as expected.

4 thoughts on “PivotItems Are Wrong when Calculated Field Is Present

  1. The code below works fine for me even with Calculated fields and gives expected results

    Sub ListPivotItems()
    Dim Pt As PivotTable
    Dim PtFld As PivotField
    Dim PtItms As PivotItems
    Dim PtItm As PivotItem
    Set Pt = Sheet1.PivotTables(1)
    Set PtFld = Pt.PivotFields(“PRODUCT”)
    Set PtItms = PtFld.PivotItems

    For Each PtItm In PtItms

    Debug.Print PtItm.LabelRange.Address, PtItm.Name

    Next

    End Sub

  2. Opps posted the wrong code….works fine

    Sub ListPivotItems()
    Dim Pt As PivotTable
    Dim PtFld As PivotField
    Dim PtItms As PivotItems
    Dim PtItm As PivotItem
    Set Pt = Sheet1.PivotTables(1)
    Set PtFld = Pt.PivotFields(“PRODUCT”)
    Set PtItm = PtFld.PivotItems(1)

    Debug.Print PtItm.LabelRange.Address, PtItm.Name
    Debug.Print Cells.Find(What:=PtItm.Name).Address, PtItm.Name

    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *