Excel pivotitem selection using cell reference 1

[owentmoore]

Hi all,

I've taken the code below from thread707-694278 in which Skip describes how to select a pivot item based on a value in a cell.

The code works fine when the value in the cell i'm selecting is a text value. However when I try enter a number, the code fails as the value is not being read, the code executing deselects all pivot items which is not allowed as there must be at least one pivot item selected.

Is there anyhing can be done to change this?

Sub SelectRef()
    For Each pit In ActiveSheet.PivotTables("PivotTable1").PivotFields("1").PivotItems
      With pit
          .Visible = True
      End With
    Next
    For Each pit In ActiveSheet.PivotTables("PivotTable1").PivotFields("1").PivotItems
      With pit
        If .Value = [TName].Value Then
          .Visible = True
        Else
          .Visible = False
        End If
      End With
    Next
End Sub

Thanks
Owen

 

[SkipVought]

Hi,

How about posting some sample source data that illustrates this issue?

Skip,

_[red][/red]

 

[owentmoore]

This is my sample data with 1, sa, 32 and 12 as the row headings.

1 sa 32 12
2 ds 51 32
3 e 2 52
4 w 8151 72
om sa 151 92
6 fs 11 112
rr as 15 132
09 sd 1 152
ee sd 111 172
10 sd 1581 192

If I enter "1" in the cell that TName refers to i get the error. If I enter "om" or "rr" or "ee" the code works and only that pivotitem is selected.

 

[SkipVought]

What fields do you have in what areas (ie describe your PT)



Skip,

_[red][/red]

 

[SkipVought]

You PivotField("1") has no value of "1" AND you must have al least ONE visible value.

Skip,

_[red][/red]

 

[owentmoore]

Sorry Skip I didn't take the time to describe the issue properly last night. An example of my table is below.

My Page filed contains "Description"
the first column in my pivot is "Part Number" and the second column is "Times"
"Quantity" is in my Data field.

Part Number Description Times Quantity
2 ds 51 32
3 e 2 52
4 w 8151 72
om sa 151 92
6 fs 11 112
rr as 15 132
09 sd 1 152
ee sd 111 172
10 sd 1581 192

 

[SkipVought]

"This is my sample data with 1, sa, 32 and 12 as the row headings"


"Part Number Description Times Quantity"


"For Each pit In ActiveSheet.PivotTables("PivotTable1").PivotFields([red]"1"[/red]).PivotItems"


Are you playing games with me? Do you believe that my time is not important?

"...I didn't take the time to describe the issue properly last night..."

You are all over the place. This is a site for PROFESSIONALS. You've changed your story about as many times a John Kerry.

If you want to go on, then start from the beginning. Explain the situation CLEARLY, CONCISELY and COMPLETELY!

Make sure that examples are CONSISTENT!



Skip,

_[red][/red]

 

[owentmoore]

I start trialing the code I use on "made up" situations which the first example I gave to you was. This allows me to not mess up my master data. Once I prove out the code on this sample data I can then apply to the actual data. Hence the changing of examples I gave you. I should have given you the detailed example first.

The pivotFields("1") is not the issue as I have changed this to suit my headings.

Don't be so hard on John Kerry - he's just misunderstood.

Remember - the code does work correctly for text values, just not for numbers

Owen

 

[SkipVought]

Part Numbers are not really numbers that you do math on. They are in reality TEXT identifiers. Text 1 and number 1 are totally different. I got wrapped around the axel on the pivot field "1" which is irrelevant and the pivot item VALUE of 1 or "1" which is significant.

So using a NUMBER in TName will NOT work.

Using a STRING will.



Skip,

_[red][/red]

 

[owentmoore]

Skip

Does this mean I need to add ' in front of all my numebrs such as in the example above, "3" becomes "'3"?

I've tried this and it does work.

Thanks
Owen

 

[SkipVought]

You answered your own question.

Skip,

_[red][/red]