custom numberformat in pivot tables

[AnnetteTR]

Hello </SPAN><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
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></o>
E.g. Choose to download "Airvolume" one also gets the value "m³/h", choose "Area" one also gets the value "m²".<o></o>
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></o>
<o></o>
If I hard code it looks like this:

With ActiveSheet. PivotTables ("ItemList").PivotFields ("Sum of" & Andetvalg)
.NumberFormat = "#, ## 0" ' m ³/h "" " <o:p></o:p>
End With<o:p></o:p>

<o></o>
It works.<o></o>
<o></o>
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></o>
But how do I get it to work?
<o></o>
I've tried this:<o></o>

With ActiveSheet. PivotTables ("ItemList").PivotFields ("Sum of" & Andetvalg)
.NumberFormat = "#, ## 0" & varVal<o:p></o:p>
End With<o:p></o:p>

<o></o>
<o></o>
Excel will not accept this syntax.<o></o>
<o></o>
I have tried many different combinations, but can not get it to work.
<o></o>
Hope You can help.
<o></o>
Best Regards Annette
<o></o>
PS <o></o>
varVal is a string

 

[cornflakegirl]

Annette

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

This code just worked for me:

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

 

[AnnetteTR]

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

 

[cornflakegirl]

This worked for me:

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

 

[AnnetteTR]

Thank you Emma

Now it works.

Regards Annette