Userinput in calculated pivot field

[AnnetteTR]

Hi there<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am using a macro to create a pivottable. In that pivottable I will use a calculated field.<o></o>
The formula for the calculated field goes like this:<o></o>

Pt.CalculatedFields.Add "UtilityRoomArea", "='AirVolume max' *6/1000", True<o:p></o:p>
Pt.PivotFields("UtilityRoomArea ").Orientation = xlDataField<o:p></o:p>
<o:p></o:p>

Now I want to exchange the value "6" with a user input.<o></o>

Dim TRfaktor As Integer<o:p></o:p>
TRfaktor = InputBox("How advanced are the units ?")<o:p></o:p>
Pt.CalculatedFields.Add "UtilityRoomArea", "='AirVolume max' *&TRfactor/1000", True<o:p></o:p>

But this syntax gives me the error:<o></o>
Unable to get the PivotFields property of the PivotTable class!!<o></o>
<o></o>
What is the right syntax?

Regards Annette

 

[Sandeep Warrier]

Probably

Pt.CalculatedFields.Add "UtilityRoomArea", "='AirVolume max' *" & TRfactor & "/1000", True

 

[AnnetteTR]

Thank you - there you go