Tech Support Guy banner
  • Please post in our Community Feedback thread for help with the new forum software! If you are having trouble logging in, please Contact Us for assistance.
Status
Not open for further replies.
1 - 2 of 2 Posts

·
Registered
Joined
·
6 Posts
Discussion Starter · #1 ·
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
 

·
Registered
Joined
·
6 Posts
Discussion Starter · #2 ·
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
 
1 - 2 of 2 Posts
Status
Not open for further replies.
Top