Pivot Table programming problem

I have set up a Pivot with a page field (ProjectCode) so as to total 5
different fields for a given Project. I then use a Module Level Public
Function to change this
ActiveSheet.PivotTables("PivotTable1").PivotFields("Project").CurrentPage =
<the function parameter> and return the 5 values to the program that called
the Function.

If the parameter value exists in the Pivot field values my code works fine.
If it does not exist in the list though I get an error message at runtime. Is
there a way to get around this situation? How can I test if the parameter
exists in the list of the Pivot field values?

Dim pvt as PivotTable
Dim pitm as PivotItem
Dim s as String
s = "abc"
Set pvt = ActiveSheet.PivotTables("PivotTable1")
For Each pitm In pvt.PageFields("Project").PivotItems
If lcase(pitm.Value) = lcase(s) Then
pvt.PageFields(1).CurrentPage = pitm.Value
Exit For
End If
Next

I usually try to use the value of the pivot item to actually set the
pagefield as I have had problems myself trying to set it to the string value
(eventhough it appears to be a match).


--
Regards,
Tom Ogilvy



"DoctorG" wrote:

> I have set up a Pivot with a page field (ProjectCode) so as to total 5
> different fields for a given Project. I then use a Module Level Public
> Function to change this
> ActiveSheet.PivotTables("PivotTable1").PivotFields("Project").CurrentPage =
> <the function parameter> and return the 5 values to the program that called
> the Function.
>
> If the parameter value exists in the Pivot field values my code works fine.
> If it does not exist in the list though I get an error message at runtime. Is
> there a way to get around this situation? How can I test if the parameter
> exists in the list of the Pivot field values?

Thanks a lot Tom... A precise and complete reply, as always.

"Tom Ogilvy" wrote:

> Dim pvt as PivotTable
> Dim pitm as PivotItem
> Dim s as String
> s = "abc"
> Set pvt = ActiveSheet.PivotTables("PivotTable1")
> For Each pitm In pvt.PageFields("Project").PivotItems
> If lcase(pitm.Value) = lcase(s) Then
> pvt.PageFields(1).CurrentPage = pitm.Value
> Exit For
> End If
> Next
>
> I usually try to use the value of the pivot item to actually set the
> pagefield as I have had problems myself trying to set it to the string value
> (eventhough it appears to be a match).
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "DoctorG" wrote:
>
> > I have set up a Pivot with a page field (ProjectCode) so as to total 5
> > different fields for a given Project. I then use a Module Level Public
> > Function to change this
> > ActiveSheet.PivotTables("PivotTable1").PivotFields("Project").CurrentPage =
> > <the function parameter> and return the 5 values to the program that called
> > the Function.
> >
> > If the parameter value exists in the Pivot field values my code works fine.
> > If it does not exist in the list though I get an error message at runtime. Is
> > there a way to get around this situation? How can I test if the parameter
> > exists in the list of the Pivot field values?