Solved: Test if a Column Field Exists in Pivot Table

[Djblois]

I am trying to test the PivotTable to see if I have already added the Date field. This is the code I am using:

if Not PT.ColumnFields = "Date" Then
    ptReportOptions objDataForm
        End If

However, it says that object doesn't support that property. PT is the pivottable that I have created and ptReportOptions is the sub I want it to run if it works. Unfortunately it crashes on the first line there

[rory]

You need something like:

   Dim pt As PivotTable, pf As PivotField
   Set pt = ActiveSheet.PivotTables(1)
   For Each pf In pt.ColumnFields
    If pf.Name = "Date" Then
        ptReportOptions objDataForm
        Exit For
    End If
   Next pf

[Djblois]

Rory,

Thank you for the help. However, that won't accomplish what I want. What I ultimately want is if the Date field doesn't exist yet, I want to add it. If it does exist I don't want it to do anything. That way if there is another column field that isn't date it (even if date was added) it will add the date column again and crash. It is closer than I have got it but not perfect yet.

[rory]

What do you mean by "if the Date field doesn't exist yet"? If it is an available field, you can just use:

PT.Pivotfields("Date").Orientation = xlColumnField

which will add it if it's not in the pivot table and won't do anything if it's already a column field. Otherwise, you could use the loop to check for it like this:

   Dim pt As PivotTable, pf As PivotField, blnFound As Boolean
   Set pt = ActiveSheet.PivotTables(1)
   For Each pf In pt.ColumnFields
    If pf.Name = "Date" Then
        blnFound = True
        Exit For
    End If
   Next pf
   If Not blnFound Then
    ' Date field not present
   Else
    ' Date field already is a column field.
   End If

[Djblois]

Thank you rory, that is interesting and I will test. I also came up with a different way to test it:

[VBA]Dim findstring As Range

Set findstring = workingSheet.Range("B1:H1").Find(What:="Date*")
On Error Resume Next
If findstring Is Nothing Then
ptReportOptions objDataForm
End If[/VBA]