For Each Loop with Pivot Table
 

Hi,

In trying to overcome the problems associated with corrupted pivot data (as
described in my post VB corrupting pivot tables), I think I know how to solve
the problem, but lack the vba expertise to code it. I would greatly
appreciate if someone could suggest the correct syntax:

I have a pivot table page field called "SA". Using VBA I would like to step
through each value in the field to compare with a user inputed value. The
value in this instance is a name.

So for example, assuming the user enters the name Alex. I would like to loop
through all the values stored in SA to see if there is match. If there is a
match I would like to set the value of SA to Alex. If there is not a match, I
would like to set the value to (blank).

I assume that a For Each statement would do this, but am struggling with the
syntax.

Thanks for your help.

Ra

I've done this using similar code to what's below (sorry, it's too late
to go looking up the specifics).

Dim piItem as PivotItem
Dim pfField as PivotField

Set ptField = ActiveSheet.PivotTables(1).PivotFields("SA")

For Each piItem in pfField.PivotItems
If piItem.Text = "Alex" Then
pfField.Value = "Alex"
Exit For
End If
Next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


RestlessAde wrote:
Hi,

In trying to overcome the problems associated with corrupted pivot data (as
described in my post VB corrupting pivot tables), I think I know how to solve
the problem, but lack the vba expertise to code it. I would greatly
appreciate if someone could suggest the correct syntax:

I have a pivot table page field called "SA". Using VBA I would like to step
through each value in the field to compare with a user inputed value. The
value in this instance is a name.

So for example, assuming the user enters the name Alex. I would like to loop
through all the values stored in SA to see if there is match. If there is a
match I would like to set the value of SA to Alex. If there is not a match, I
would like to set the value to (blank).

I assume that a For Each statement would do this, but am struggling with the
syntax.

Thanks for your help.

Ra

Thanks Jon. I implemented a work around in the end, but I'll try this also.

"Jon Peltier" wrote:

I've done this using similar code to what's below (sorry, it's too late
to go looking up the specifics).

Dim piItem as PivotItem
Dim pfField as PivotField

Set ptField = ActiveSheet.PivotTables(1).PivotFields("SA")

For Each piItem in pfField.PivotItems
If piItem.Text = "Alex" Then
pfField.Value = "Alex"
Exit For
End If
Next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


RestlessAde wrote:
Hi,

In trying to overcome the problems associated with corrupted pivot data (as
described in my post VB corrupting pivot tables), I think I know how to solve
the problem, but lack the vba expertise to code it. I would greatly
appreciate if someone could suggest the correct syntax:

I have a pivot table page field called "SA". Using VBA I would like to step
through each value in the field to compare with a user inputed value. The
value in this instance is a name.

So for example, assuming the user enters the name Alex. I would like to loop
through all the values stored in SA to see if there is match. If there is a
match I would like to set the value of SA to Alex. If there is not a match, I
would like to set the value to (blank).

I assume that a For Each statement would do this, but am struggling with the
syntax.

Thanks for your help.

Ra