VBA not recognizing "PivotFields"

K

kitsu_ne

New member
Joined
Jan 15, 2014
Messages
16
Reaction score
0
Points
0
Location
http://www.khanacademy.org/profile/killervulpix/#p
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:
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:
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
 
You must log in or register to reply here.
Back
Top