Solved

VBA error when filtering pivottable-rows: 1004

Posted on 2013-06-03
3
875 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

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