Solved

Selecting a datarange based on the value of a pivot item

Posted on 2014-02-25
17
476 Views
Last Modified: 2014-03-11
I have a pivot table (image below) with three row fields. I would like to select a data range using the value of the second row field, and then format the selected range based on that value. The problem is when I iterate through the pivot items in the second row field, my code lists all the items in the field, not the one item associated with rowfield (1) value.  I can't seem to search based on a hierarchy like this:

for each item in rowfield(1)
    if rowfield(2).pivotitem value = "x" then
         select and format for x
   else
          leave format alone
  end if


Using an index to the pivot items of rowfield two doesn't return only the item for that field, it returns all values in the domain for the field. I'd like to base the action on the value of the item in the second row field, but I can't seem to query the value of that item alone.
Thanks
pt image
0
Comment
Question by:RJT_VT
  • 10
  • 7
17 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39909432
I see this question has been unanswered. Do you still need help ? If yes then can you post a sample workbook that have the data you pictured ?

gowflow
0
 

Author Comment

by:RJT_VT
ID: 39917492
The problem has not been solved. Attached is a workbook with a similar pt, and data.
Note that the pivot table here is based on a recordset extract of the data in the sheet named FMOutput, I apologize for not responding sooner. I didn't realize there was a comment.
FM-1.2.111.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39917520
ok lets agree then that if you want a solution we should keep the pace faster and more prompt. Just check your email and when there is a comment on one of the question you asked there should be an email sent (maybe it is in your junk mail check there too.)

Pls confirm understanding basis which I will then look at this more in details.
gowflow
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:RJT_VT
ID: 39917532
Confirmed. Believe me, I'm anxious for a solution. I have no record of an email from your 03/06 comment. I logged on today expecting to close the request.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39917542
ok fine no problem. Did you check now ?? you should have an email with my last reply and this one as well. If you still did not receive anything, I suggest you bring this to customer service they will look it up for you.

Will look at your file.
gowflow
0
 

Author Comment

by:RJT_VT
ID: 39917556
I did get your responses via email. Thank you.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39917566
ok great.

Letts get to specifics now.

You mention in your post:
I would like to select a data range using the value of the second row field, and then format the selected range based on that value.

In you sheet you have
Pulp (Cords)
Sawlog (BF)

which one you want to iterate ?
gowflow
0
 

Author Comment

by:RJT_VT
ID: 39917589
It is the Unit row field, not the column fields.  I'd like to format the ranges differently...cords with tenths and BF with round numbers...but first I need to select the data range. I could do this by iterating through and selecting based on a string search of Unit pivot item, but there must be an easier way.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39917591
Sorry I need to get some things done will be back on this in few hours. meantime pls state specifically based on the file you posted what are the lines or circumstances you need to format and how

Will be back
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39917596
our replies just crossed.

Not sure I understand please be more specific as to what data need to become what then when I understand what you want I can lead you to how to do it.
gowflow
0
 

Author Comment

by:RJT_VT
ID: 39917865
As I state in my initial post, I want to use VBA to select a data range associated with a single pivot item value, say, Units=cords, where Units is a row field with pivot items for each type of unit (cords, bf, etc). I can select manually, but it does not work to simply record a macro. Once a data range is selected, I simply want to modify the number format.

Look over my initial problem statement. "Units" is the second row field. Is there a way to use VBA to select the data range associated with Units= "cords"?  

I can iterate through the rowfields, but I'm having trouble iterating through the values of a pivot item. As stated above,...Using an index to the pivot items of rowfield "Units" doesn't return a single item for that field---the current pivotitem value where i can test for unit= "cords"--, it returns all values in the domain of item values for the field. I'd like to base the action on the value of the item in the second row field, but I can't seem to query the value of that item alone.
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 500 total points
ID: 39919823
ok sorry for delay due to time zone diff.

here is some code that may help you get what you want (all is in the attached workbook)

1) to iterate thru all items in the pivot table Tbl

Dim Tbl As PivotTable

'---> Iterate thru Pivot items Row/Col
        For I = 0 To Tbl.PivotRowAxis.PivotLines.Count
            For J = 1 To Tbl.PivotColumnAxis.PivotLines.Count
                Debug.Print "Col " & J & " Row " & I & " " & Tbl.ColumnRange.Cells(I, J)
            
            Next J
        Next I

Open in new window


2) To iterate thru a specific field

Dim Tbl As PivotTable
Dim PField As PivotField
Dim PItem As PivotItem

'---> Iterate thru Fields
        For Each PField In Tbl.PivotFields
            Debug.Print PField.Caption
            If PField = "Unit" Then
                '---> Iterate thru specific item
                For Each PItem In PField.PivotItems
                    For Each cCell In PItem.DataRange
                        Debug.Print "Item  " & PItem.Caption & " value " & PItem.Value & " Cell " & cCell.Address & " Value " & cCell.Value
                    Next cCell
                Next PItem
            End If
        Next PField

Open in new window


In the attached WB you have a sub called test all the data will display in the immediate window. I suggest you runt the code by step F8 then F8 so you see the instructions running and the results being printed in the immediate window.

Let me know if this helps and you need something more specific.
gowflow
FM-1.2.111.xlsm
0
 

Author Comment

by:RJT_VT
ID: 39920491
I was missing the looping through the item caption (somehow!)
Here's what I developed from your suggested code:
   
Dim cCell As Range
    Dim PItem As PivotItem
    With PT.PivotFields("Unit")
         For Each PItem In PT.PivotFields("Unit").PivotItems
              For Each cCell In PItem.DataRange
                  If PItem.Caption = "tons" Then   'once the desired item caption is found...
                    PItem.DataRange.Select         '... select the data range and format
                    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""?_);_(@_)"
                    Exit For                        'ignore any other cells in the item--they're all the same
                  End If
              Next                                    'next item
          Next
     End With
    

Open in new window

Thank you!  I'm more than happy to award points, but if this is easy: the selection doesn't include the total row and total column. I can use .offset or .entirerow to extend, but I don't see an easier method. Any suggestion?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39920597
Well I tried, but frankly not too much in depth in Pivots so presume easiest would be to use offset just after you finish looping with the item.

I am sure there must be an easier way, ... but  :(
If I find something will let you know in here.

gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39920664
ok got it !!

This should select all the Subtotals in the Pivot table Tbl

Tbl.PivotSelect "'" & PItem.Name & "' '" & PField.Name & "'[All;Total]", xlDataAndLabel, True

after selection is done apply something like
Selection.Font.Bold = True
or ...
Selection.Font.ColorIndex = 3
will color in Red all the Data of the Subtotals

gowflow
0
 

Author Comment

by:RJT_VT
ID: 39920922
Thanks a bunch!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39921314
Your welcome. You can post a link in this question for any other question you may need help with, I will keep it monitored.
gowflow
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Photo Albums in PowerPoint Photo Albums are a very useful tool in PowerPoint and allow you quickly add a large number of images. The images can be formatted in a variety of ways so that you are able to create a professional looking presentation v…
As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

777 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