Results 1 to 3 of 3

Thread: VBA not recognizing "PivotFields"

  1. #1
    Seeker kitsu_ne's Avatar
    Join Date
    Jan 2014
    Location
    http://www.khanacademy.org/profile/killervulpix/#programs
    Posts
    16
    Articles
    0

    VBA not recognizing "PivotFields"



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I've nearly finished a button that modifies the values of a pivot table. I had it working at one point, and now I don't know what I've messed up.

    The statement "Set pf = pt.PivotFields("JI")" does not seem to be working correctly, even though it once would. When I execute the button, the function runs nearly perfectly, but stops at the end when it references pf.PivotItems
    By this I mean, when it gets to the line "For Each pi In pf.PivotItems" It displays the error "Run-Time Error '91': Object Variable or Withblock variable not set"

    Can you see why "Set pf = pt.PivotFields("JI")" isn't working? Is it because I have the "JI" field as a Filter, rather than a row/column field??? When you hover over it it shows "pf = "Nothing".

    Also, JI is set to allow multiple items to be filtered / displayed.

    Thanks...

    Code:
    Sub MDC_WTEST()
    
    MDC_TableUpdate "A", "G", "P"     'These are the values we want to display, they are used later in the function.
    
    End Sub


    Code:
     
    Function MDC_TableUpdate(val1 As String, Optional val2 As String, Optional val3 As String)
    
        Dim pt As PivotTable
        Set pt = ActiveSheet.PivotTables("MDC_Table")
    
        Dim pf As PivotField
        Set pf = pt.PivotFields("JI")
    
        Dim pfDate As PivotField
        Set pfDate = pt.PivotFields("CREATE")
    
        Dim stDate As Date
        stDate = MDC_GetDate("Start")
            
        Dim edDate As Date
        edDate = MDC_GetDate("End")
    'Before we can update the pivot table we will need to reset it (clear the filters).
    '  We will also need to set it up so that  "WES" jobs do not dispay.
    
        pt.ClearAllFilters
        pt.PivotFields("WES").CurrentPage = "(blank)"
    
    ' CurrentPage tells the pivot table to filter the WES item by one value,
    '   "(blank)", which is the main job we want to look at.  If there is something else we don't want it.
    
    'This next bit checks to ensure all pivot fields are visible.  If they are not, it fixes that.
        For Each pf In pt.PivotFields
            For Each pi In pf.PivotItems
                If pi.Visible = False Then
                    pi.Visible = True
                End If
            Next pi
        Next pf
    
    
    'Now we make all dates that do not fit the date range become invisible.
        For Each pi In pfDate.PivotItems
          'goes through every PivotItem in the PivotField
            If Not pi.Name = "(blank)" Then
              'ignores blanks and then checks if date is in range
                If pi.Visible = True Then
                    If pi.Value < stDate Then pi.Visible = False
                    If pi.Value > edDate Then pi.Visible = False
                End If
            End If
        Next pi
     
     'We do the same for the value(s) we want to display.
        For Each pi In pf.PivotItems                           'This is where the code gets highlighted as an error.
            If pi.Value <> val1 Or pi.Value <> val2 Or pi.Value <> val3 Then pi.Visible = False
        Next pi
    End Function
    ~Kitsu
    Last edited by kitsu_ne; 2014-02-10 at 12:48 PM. Reason: Made it easier to read

  2. #2
    Seeker kitsu_ne's Avatar
    Join Date
    Jan 2014
    Location
    http://www.khanacademy.org/profile/killervulpix/#programs
    Posts
    16
    Articles
    0
    I may have figured it out. I changed it to the following:
    Code:
        Dim pfField As PivotField
        "Set pfField = pt.PivotFields("JI")"
    And it went through it this time. It stopped at the end because I am thinking that it is confused with a value or something. It makes all the correct values invisible, but then it stops at "Then pi.Visible = False" and displays the error "Unable to set the visible property of the PivotItem class"

    I modified the end to check if the pi.Visible was true first, thinking that would solve the problem but it didint.
    Code:
            If pi.Visible = True Then
                If pi.Value <> val1 Or pi.Value <> val2 Or pi.Value <> val3 Then pi.Visible = False
            End If
    I'll keep working at it.



    ~Kitsu
    Last edited by kitsu_ne; 2014-02-10 at 01:07 PM.

  3. #3
    Seeker kitsu_ne's Avatar
    Join Date
    Jan 2014
    Location
    http://www.khanacademy.org/profile/killervulpix/#programs
    Posts
    16
    Articles
    0
    It wouldn't let me edit my previous posts, but I figured out a way to get this to work. Can anyone help me find a better way because this is messy, in my oppinion.

    Code:
        For Each pi In pfField.PivotItems
            If pi.Visible = True Then
                If pi.Value <> val1 Then
                    If pi.Value <> val2 Then
                        If pi.Value <> val3 Then pi.Visible = False
                    End If
                End If
            End If
        Next pi
    ~Kitsu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •