PDA

View Full Version : [SOLVED] Pivot "For Each" Filtering Loop Failing



Phelony2
09-10-2013, 02:33 AM
Hi Guys

I've been trying to get below code to work and have run into a brick wall :banghead:.

I keep getting a type mismatch for the variable "pivitem" which I've tried as everything from a string to a bollean and everything in between and it refuses to budge. Every other variable seems to know what it should be doing except that one!

The input for loclist will ultimately come from a listbox in a userform, but for the time being I'm using a cell reference for ease of testing.

The names of sheets and pivot tables come from lists generated in the workbook itself on start up, the workbook is designed to be a report template that can be applied to any future reporting requirement, so the flexibility of being able have pivot filters than run without having to be hard coded is the objective.

I'm sure I have screwed up somewhere, but I've been looking at this for ages and can't find a way out.

Any ideas?

Thanks

Phel


Private Sub filterapply_click()

Dim loclist As String
Dim fldname As String
Dim pagetarget As Range
Dim pivtarget As Range
Dim i As Integer
Dim pivitem As PivotItem


'this section will be removed when applied to the form code
'at present for testing is running from a worksheet
'**********************************************************
'selected item becomes variable
loclist = Sheets("options").Range("A4").Value
'item is then cleared on sheet but held as variable - deactivated for testing
'Sheets("options").Range("A4").Value = ""
'select field name from existing list
fldname = Sheets("options").Range("B4").Value
'pivotitems defined
'**********************************************************
'for each worsheet
For Each pagetarget In Sheets("Initialsetup").Range("B2:B41")
If pagetarget.Value = "" Then
Exit For

Else

'for each pivottable
For Each pivtarget In Sheets("Initialsetup").Range("C2:C41")
If pivtarget.Value = "" Then
Exit For

Else

'for each pivot item
'*****this section keeps getting a type mismatch error******


For Each pivitem In _
Sheets(pagetarget).PivotTables(pivtarget).PivotFields(fldname).PivotItems
If pivitem = "" Then

Exit For

Else

'hide and show pivot items
Select Case pivitem
Case loclist
pivitem.Visible = True
Case Else
pivitem.Visible = False
End Select


'next next next
End If
Next pivitem
End If
Next pivtarget
End If
Next pagetarget


'additional code goes here
End Sub

Phelony2
09-10-2013, 08:22 AM
Fixed this by just making it simpler. Bog standard loop and variables without trying to make it cleaner.

Still trips itself up, but that's simple bug fixing.


Sub filterpivots2()
Dim LTarg As String
Dim PFTarg As String
Dim PvTab As String
Dim ws As String
Dim RPFTarg As Range
Dim RPvTab As Range
Dim Rws As Range
Dim pivitem As PivotItem
'set initial workbook
Set Rws = Sheets("initialsetup").Range("b1")
'set initial pivot table
Set RPvTab = Sheets("initialsetup").Range("c1")
'set pivot field
Set RPFTarg = Sheets("Dropdowns").Range("A1")
'set pivot item target (location)
LTarg = Sheets("options").Range("A4")
ws = Rws.Value
PvTab = RPvTab.Value
PFTarg = RPFTarg.Value

Do Until ws = ""

Set Rws = Rws.Offset(1, 0)
Set RPvTab = RPvTab.Offset(1, 0)

ws = Rws.Value
PvTab = RPvTab.Value

For Each pivitem In _
Sheets(ws).PivotTables(PvTab).PivotFields(PFTarg).PivotItems
If pivitem = "" Then

Exit For

Else
'hide and show pivot items
Select Case pivitem.Name
Case LTarg
pivitem.Visible = True
Case Else
pivitem.Visible = False
End Select


End If
Next pivitem

Loop

End Sub