?
Solved

VBA - Pivot Table For Each Out of Range due to "unused values"

Posted on 2011-04-20
7
Medium Priority
?
626 Views
Last Modified: 2012-05-11
Experts,

See attached, where PivotTable1 had some formatting:

    Set PT = ActiveSheet.PivotTables(1)
Set PF = PT.RowFields(1)
For Each PI In PF.VisibleItems
PI.LabelRange.BorderAround _
Weight:=xlMedium
Debug.Print PI.LabelRange.Address

Open in new window


My RowFields(1) Had the following Plans:
PLAN A
PLAN B
PLAN C
PLAN D
PLAN E

Now I have updated my data (Sheet1!$D$2:$E$8) and this time I only have:
PLAN A
PLAN C
PLAN D
PLAN E

Stepping through, the code works for PLAN A, but then "can't locate" PLAN B, so it errors, message 1004 - Unable to get the LabelRange property of the PivotItem Class.

How do I go through the LableRange but only those that have a value. I thought that the "PF.VisibleItems" would have covered this.

Thank you,
Kevin
Option Explicit
Sub FormatSalesByProduct()

Dim PT As PivotTable
Dim PF As PivotField
Dim PI As PivotItem

    Set PT = ActiveSheet.PivotTables(1)
    Set PF = PT.RowFields(1)
        For Each PI In PF.VisibleItems
            PI.LabelRange.BorderAround _
                Weight:=xlMedium
        Debug.Print PI.LabelRange.Address
    Next PI

    Set PT = ActiveSheet.PivotTables(1)
    Set PF = PT.RowFields(2)
        For Each PI In PF.VisibleItems
            PI.LabelRange.BorderAround _
                Weight:=xlMedium
        Debug.Print PI.LabelRange.Address
    Next PI

End Sub

Open in new window

EE-PivotTable-Error-Missing-Row1.xls
0
Comment
Question by:redrumkev
  • 4
  • 3
7 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35435871
Try:
Sub FormatSalesByProduct()

Dim PT As PivotTable
Dim PF As PivotField
Dim PI As PivotItem

    Set PT = ActiveSheet.PivotTables(1)
    Set PF = PT.RowFields(1)
    On Error Resume Next
    For Each PI In PF.VisibleItems
        PI.LabelRange.BorderAround Weight:=xlMedium
    Next PI

    Set PF = PT.RowFields(2)
    For Each PI In PF.VisibleItems
        PI.LabelRange.BorderAround Weight:=xlMedium
    Next PI

End Sub

Open in new window

0
 
LVL 23

Author Comment

by:redrumkev
ID: 35436138
Rorya,

That worked, to skip the errors, but after changing things (see I changed the PLAN TYPES) around and refreshed the table, now ABC (H5:H6) the formatting is not working.

Attached -->
Debug.Print PI.LableRange.Address shows:

$G$5:$G$6
$G$7:$G$10
$G$11:$G$12
$G$13:$G$14
$H$5:$H$8,$H$11:$H$14
$H$9:$H$10

So column G is correct.
But column H should be:
$H$5:$H$6
$H$7:$H$8
$H$9:$H$10
$H$11:$H$12
$H$13:$H$14

Do I need to start over again after updating RowFields(1) is that why Excel see's the range differently???

Kevin
EE-PivotTable-Not-All-Items-Bord.xls
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35436248
Try this version:
Sub FormatSalesByProduct()

Dim PT As PivotTable
Dim PF1 As PivotField, PF2 As PivotField
Dim PI1 As PivotItem, PI2 As PivotItem

    Set PT = ActiveSheet.PivotTables(1)
    Set PF1 = PT.RowFields(1)
    Set PF2 = PT.RowFields(2)
    On Error Resume Next
        For Each PI1 In PF1.VisibleItems
            PI1.LabelRange.BorderAround _
                Weight:=xlMedium
            For Each PI2 In PF2.PivotItems
                Intersect(PI2.LabelRange, PI1.LabelRange.EntireRow).BorderAround _
                    Weight:=xlMedium
            Next PI2
    Next PI1

End Sub

Open in new window


HTH
Rory
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Author Comment

by:redrumkev
ID: 35443867
Rory,

THAT WORKED!!

Can you please help me to understand the second For Each Statement:

I understand that there are two variables for PivotItem with the second using PI2. But what (how and why) does the Intersect function do? And why does it reference back to PI1?

Kevin
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35446585
For each item in field 1 it loops through each item in field 2 then adds borders for the label range in field2 that relates to field 1. This takes care of the cases where the same item in field2 appears consecutively for 2 different items in field 1 - otherwise the code would treat field 2 as one contiguous range and only border round the outside of the whole block. Make sense?
0
 
LVL 23

Author Comment

by:redrumkev
ID: 35448760
Rory,

Yes, that does make sense, thank you for explaining it!!!

Have a good weekend,
Kevin
0
 
LVL 23

Author Comment

by:redrumkev
ID: 35694167
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

850 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