custom numberformat in pivot tables

AnnetteTR

Board Regular
Joined
Aug 19, 2010
Messages
85
Hello </SPAN><?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
I am about to make a larger macro, where I ask the user to specify which output you want, based on the selection also the value in the adjacent cell is retrieved, which will be used to put the unit on the number you get.<o:p></o:p>
E.g. Choose to download "Airvolume" one also gets the value "m³/h", choose "Area" one also gets the value "m²".<o:p></o:p>
When I insert the value I want the formatting so that for example the Airvolume is shown as "2500 m³/h" and Area is shown as "25 m²".<o:p></o:p>
<o:p></o:p>
If I hard code it looks like this:
Rich (BB code):
With ActiveSheet. PivotTables ("ItemList").PivotFields ("Sum of" & Andetvalg)
.NumberFormat = "#, ## 0" ' m ³/h "" " <o:p></o:p>
End With<o:p></o:p>
<o:p></o:p>
It works.<o:p></o:p>
<o:p></o:p>
But now I do not know in advance if "Andetvalg" is Airvolume or Area, therefore the Unit is retrieved and assignet to the variable called "varVal", I have checked and the value is correct. <o:p></o:p>
But how do I get it to work?
<o:p></o:p>
I've tried this:<o:p></o:p>
Rich (BB code):
With ActiveSheet. PivotTables ("ItemList").PivotFields ("Sum of" & Andetvalg)
.NumberFormat = "#, ## 0" & varVal<o:p></o:p>
End With<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Excel will not accept this syntax.<o:p></o:p>
<o:p></o:p>
I have tried many different combinations, but can not get it to work.
<o:p></o:p>
Hope You can help.
<o:p></o:p>
Best Regards Annette
<o:p></o:p>
PS <o:p></o:p>
varVal is a string
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Annette

You may just need more "s round your units when setting the value of varVal.

This code just worked for me:

Code:
Sub test()

Dim sUnits As String, sFormat As String

sUnits = """ m2"""
sFormat = "#,##0" & sUnits

Sheets("Sheet2").PivotTables("PivotTable1").PivotFields("Sum of Thing2").NumberFormat = sFormat

End Sub
 
Upvote 0
Hi Emma
Thank you for your answer - thing is I have retrieved the value of varVal from the user - how do I put more "s around it?
Regards Annette
 
Upvote 0
This worked for me:

Code:
Sub test()

Dim sUnits As String, sFormat As String

sUnits = " M2"
sFormat = "#,##0" & """" & sUnits & """"

Sheets("Sheet2").PivotTables("PivotTable1").PivotFields("Sum of Thing2").NumberFormat = sFormat

End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,226,693
Messages
6,192,471
Members
453,726
Latest member
JoeH57

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
Back
Top