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.
|
1 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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.
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
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
@Sam, LabelRange.Address still reported incorrectly for me using xl2010 & xl2013