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:

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