Selecting a datarange based on the value of a pivot item

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
RJT_VTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
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
RJT_VTAuthor Commented:
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
gowflowCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

RJT_VTAuthor Commented:
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
gowflowCommented:
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
RJT_VTAuthor Commented:
I did get your responses via email. Thank you.
0
gowflowCommented:
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
RJT_VTAuthor Commented:
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
gowflowCommented:
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
gowflowCommented:
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
RJT_VTAuthor Commented:
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
gowflowCommented:
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
RJT_VTAuthor Commented:
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
gowflowCommented:
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
gowflowCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RJT_VTAuthor Commented:
Thanks a bunch!
0
gowflowCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.