Solved

VBA error when filtering pivottable-rows: 1004

Posted on 2013-06-03
3
848 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
ID: 39216077
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
ID: 39224897
Bummer...You are right...Big shame on me :-(

Thx for pointing it

Steven
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39224916
:)

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

Good luck with the rest of your project.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

726 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