Pivot table number format based on title

gdel02

New Member
Joined
Apr 2, 2014
Messages
9
Working with this:
Code:
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

CMAnslow

New Member
Joined
Nov 11, 2012
Messages
45
Hey,

I think the number format for percent is :-

Code:
NumberFormat = "0.00%"

Try this and see if it works.
 

gdel02

New Member
Joined
Apr 2, 2014
Messages
9
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

New Member
Joined
Nov 11, 2012
Messages
45
Try the following and see that you have the correct name for the field.

Code:
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

New Member
Joined
Apr 2, 2014
Messages
9
I was able to solve by using the Like command:

Solution:

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

Watch MrExcel Video

Forum statistics

Threads
1,127,711
Messages
5,626,420
Members
416,183
Latest member
IanA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top