Solved

Selecting a datarange based on the value of a pivot item

Posted on 2014-02-25
17
458 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:RJT_VT
Comment Utility
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
Comment Utility
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
Comment Utility
I did get your responses via email. Thank you.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks a bunch!
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now