1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Check if pivotitem exists

Discussion in 'Business Applications' started by pthom14, Dec 21, 2010.

Thread Status:
Not open for further replies.
  1. pthom14

    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
    
     
  2. pthom14

    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
    
     
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/969871

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice