Check if pivotitem exists

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

pthom14

Thread Starter
Joined
Oct 5, 2010
Messages
6
I'm using Excel 2007 on XP. The problem I'm having revolves around an OLAP cube pivot table. The table is a P&L. The code segment below is looped through 300+ times, once for each division (a filter on the pivot table).

I'm trying to check if, for a selected division, a pivot item exists. If it exists, I want to expand the drilled down detail. If not, I want to ignore it and proceed to the next segment of code.

The code executes well for 313 of the 322 divisions. Then it trips and throws, "Run-time error '1004': Unable to get the PivotItems property of the PivotField class," on the first Set ptPF statement.

Thanks in advance for any help!

Code:
Dim ptPF As PivotItems
    On Error Resume Next
    Set ptPF = ActiveSheet.PivotTables("pvtSOO").PivotFields("[Category].[Category]").PivotItems("[Category].[All].[Enhanced Compensation]")
    On Error GoTo 0
        If ptPF Is Nothing Then
        Else
            ActiveSheet.PivotTables("pvtSOO").PivotFields("[Category].[Category]"). _
            PivotItems("[Category].[All].[Enhanced Compensation]").DrilledDown = True
        End If
    On Error Resume Next
    Set ptPF = ActiveSheet.PivotTables("pvtSOO").PivotFields("[Category].[Category]").PivotItems("[Category].[All].[Errors, Bad Debts, & Settlements]")
    On Error GoTo 0
        If ptPF Is Nothing Then
        Else
            ActiveSheet.PivotTables("pvtSOO").PivotFields("[Category].[Category]"). _
            PivotItems("[Category].[All].[Errors, Bad Debts, & Settlements]").DrilledDown = True
        End If
 

pthom14

Thread Starter
Joined
Oct 5, 2010
Messages
6
The following code worked, although it is not ideal nor the most efficient solution. If anyone knows of an alternative better solution, please post.

Code:
    On Error GoTo -1
            ActiveSheet.PivotTables("pvtSOO").PivotFields("[Category].[Category]"). _
            PivotItems("[Category].[All].[Enhanced Compensation]").DrilledDown = True
    On Error GoTo -1
            ActiveSheet.PivotTables("pvtSOO").PivotFields("[Category].[Category]"). _
            PivotItems("[Category].[All].[Errors, Bad Debts, & Settlements]").DrilledDown = True
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top