Results 1 to 5 of 5

Intersect PivotFields & RowFields

This is a discussion on Intersect PivotFields & RowFields within the Excel Questions forums, part of the Question Forums category; What I want it to do is intersect rows "Emp. Name" & "Avg Salary". I can then apply Font.Color filters ...

  1. #1
    New Member
    Join Date
    Mar 2016
    Posts
    5

    Default Intersect PivotFields & RowFields

    What I want it to do is intersect rows "Emp. Name" & "Avg Salary". I can then apply Font.Color filters to specific values down the line, but for now I can't figure out how to select the correct intersects.This is what the pivot looks like.
    Values
    Row Labels Avg. Salary Data #1 Data #2 Data #3
    Manager #1 75,000
    Emp. Name 50,000
    Emp. Name 100,000
    Manager #2 50,000
    Emp. Name 25,000
    Emp. Name 75,000
    Here is what I have so far but its not working. Essentially, it is applying the Font.Color changes to the entire "Avg Salary" Column instead of only to the "Emp Names" in "Avg Salary".

    Code:
    Dim rng1 As Range    Dim rng2 As Range
    Set rng1 = pvt.PivotFields("Avg Salary").DataRange
    Set rng2 = pvt.RowFields("Employees").DataRange.EntireRow
    Intersect(rng1, rng2).Font.Color = vbRed
    This is what I want the code to do.
    Values
    Row Labels Avg. Salary Data #1 Data #2 Data #3
    Manager #1 75,000
    Emp. Name 50,000
    Emp. Name 100,000
    Manager #2 50,000
    Emp. Name 25,000
    Emp. Name 75,000

    Any help being able to find the correct intersect would be much appreciated.

  2. #2
    New Member
    Join Date
    Mar 2016
    Posts
    5

    Default Re: Intersect PivotFields & RowFields

    I've tried every variation I can find on the web. Is it even possible to do what I'm asking?

  3. #3
    Board Regular tonyyy's Avatar
    Join Date
    Jun 2015
    Location
    Grants Pass, Oregon
    Posts
    1,107

    Default Re: Intersect PivotFields & RowFields

    Check out Jon Peltier's blog Referencing Pivot Table Ranges in VBA.

    Might be helpful.

    Cheers,

    tonyyy
    Windoze 7 / Excel 2010

    How to paste your Excel data with Excel Jeanie
    How to post your vba code

  4. #4
    New Member
    Join Date
    Mar 2016
    Posts
    5

    Default Re: Intersect PivotFields & RowFields

    Thank you. I have been using that.

    What I found is that this is getting me close.

    Set rng1 = pvt.RowFields("Employees").PivotItems("Jon James").DataRange.EntireRow Set rng2 = pvt.PivotFields("Avg Salary").DataRange


    Intersect(rng1, rng2).Font.Color = vbRed
    This intersects the salary and row field but only for the employee "Jon James." I'm assuming my next step is to create an array using the employee names and pass that arrow into .PivotItems

    I'll update the thread if I figure it out. Who knows, it may help someone down the line who is searching for a similar answer.

  5. #5
    Board Regular tonyyy's Avatar
    Join Date
    Jun 2015
    Location
    Grants Pass, Oregon
    Posts
    1,107

    Default Re: Intersect PivotFields & RowFields

    Another approach would be to add a column to your source data to differentiate your employees... for example, the column could be labeled Color, and employee attributes could be red, blue, green...

    Then your code could be...

    Code:
    Set rng1 = pvt.RowFields("Color").PivotItems("Red").DataRange.EntireRow
    That would eliminate the need for an array.
    Windoze 7 / Excel 2010

    How to paste your Excel data with Excel Jeanie
    How to post your vba code

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com