Pivot table number format based on title

[gdel02]

Working with this:

For Each pvtFld In Pvt.DataFields
    If pvtFld.Name = "*Rate*" Then
     pvtFld.NumberFormat = "%0.00"
       Else: pvtFld.NumberFormat = "#,##0"
    End If
Next pvtFld

So that for each field in a pivot table, if the pivot field name contains "Rate" it will format as a percent, otherwise a number.
The code runs correctly without any stops or errors, but the resulting chart still shows values as a number format. Any suggestions?
Thanks

 

[CMAnslow]

Hey,

I think the number format for percent is :-

NumberFormat = "0.00%"

Try this and see if it works.

 

[gdel02]

You're right that is my mistake. The problem though is that the code isn't recognizing the field title, and formats all values as a number.

 

[CMAnslow]

Try the following and see that you have the correct name for the field.

For Each pvtFld In Pvt.DataFields
    msgbox(pvtFld.Name)
    If pvtFld.Name = "*Rate*" Then
     pvtFld.NumberFormat = "%0.00"
       Else: pvtFld.NumberFormat = "#,##0"
    End If
Next pvtFld

 

[gdel02]

I was able to solve by using the Like command:

Solution:

For Each pvtFld In Pvt.DataFields
    If pvtFld.Name Like "*Rate*" Then
     pvtFld.NumberFormat = "0.00%"
       Else: pvtFld.NumberFormat = "#,##0"
    End If
Next pvtFld