Solved

VBA error when filtering pivottable-rows: 1004

Posted on 2013-06-03
3
810 Views
Last Modified: 2013-06-06
Hello,

When I'm running the following code, I always get Error 1004, 'Application defined or object defined error' when I'm coming at the line where the filter on the "Composed"-field is done:

This is the code

    Dim oWSData                                   As Worksheet
    Dim oWSPivot                                  As Worksheet

    Dim dtDateEnd                                 As Date
    Dim dtDateStart                               As Date
    Dim oRNGTemp                                  As Range
    Dim lLastRow                                  As Long
    Dim ptTemp                                    As PivotTable
    Dim ptCache                                   As PivotCache
    Dim ptTable                                   As PivotTable
    Dim ptField                                   As PivotField
    Dim ptItem                                    As PivotItem

    Set oWSData = ThisWorkbook.Sheets("temp")
    Set oWSPivot = ThisWorkbook.Sheets("pivot")

    dtDateStart = ThisWorkbook.Names("PARAM_DATE_BEGIN").RefersToRange.Value
    dtDateEnd = ThisWorkbook.Names("PARAM_DATE_END").RefersToRange.Value + 1

    lLastRow = oWSData.Range("A3").End(xlDown).Row
    Set oRNGTemp = oWSData.Range("A2:J" & lLastRow)

    Set ptCache = ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, sourceData:=oRNGTemp)
 
   Set ptTable = ptCache.CreatePivotTable(TableDestination:=oWSPivot.Range("A1"), TableName:="parkdata")

    With ptTable
        .AddFields RowFields:="Composed", ColumnFields:="farm"
        .AddDataField ptTable.PivotFields("cat"), "Sum cat", xlSum
    End With

    Set ptField = ptTable.PivotFields("Composed")
    
    ptField.ClearAllFilters
    
    For lCounterA = 1 To ptTable.PivotFields("Composed").PivotItems.Count
    Set ptItem = ptTable.PivotFields("Composed").PivotItems(lCounterA)
        If ptItem.Name >= dtDateStart And ptItem.Name >= dtDateEnd Then
            ptItem.Visible = True
        Else
            ptItem.Visible = False
        End If
    Next lCounterA

End sub

Open in new window


He is comparing tow by row and is reading it correctly. But when he wants to put it not visible, it is crashing on the line   'ptItem.Visible = True' or  'ptItem.Visible = False'


I also tried this, with the same error. And this is also crashing

ptField.PivotFilters.Add Type:=xlDateBetween, Value1:=dtDateStart, Value2:=dtDateEnd

Open in new window


Thank you for your help
0
Comment
Question by:Steven Vermoere
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 400 total points
Comment Utility
Hi,

Just an observation:

If you are looking for rows with dates between the dtDateStart & dtDateEnd values, then this line of code seems incorrect:

If ptItem.Name >= dtDateStart And ptItem.Name >= dtDateEnd Then

Open in new window


Should it be like this?...

If ptItem.Name >= dtDateStart And ptItem.Name <= dtDateEnd Then

Open in new window


BFN,

fp.
0
 

Author Comment

by:Steven Vermoere
Comment Utility
Bummer...You are right...Big shame on me :-(

Thx for pointing it

Steven
0
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
:)

Sometimes just a second pair of eyes is all it takes.

Good luck with the rest of your project.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
EXCEL Addin problem 7 21
Splitting out Data 14 27
Turning python script into an applet 12 31
Dynamic Excel Countdown Graphic 21 17
This is an explanation of a simple data model to help parse a JSON feed
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now