Dear experts,
I am having again a problem with writing a code for a calculated field of a
pivot table.
The calculation of this field is performed with a variable, a string that
should represent the ' Volume (MT) Oct_2004', which is in the code below.
This is because I will change the database every month and there will be a
November, December, etc instead of October.

ActiveSheet.PivotTables("PivotTable1").CalculatedF ields.Add
"Weighted Average PM Deviation (by Volume)", _
"='Deviation * Volume' /' Volume (MT) Oct_2004'", True
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Weighted Average PM
Deviation (by Volume)").Orientation = _
xlDataField

If I now change the code with a test variable
test = " Volume (MT) Oct_2004"
this does not work at all, Excel does not recognize it. (error meassage "an
item name cannot be found", but I am sure the string is correct)

Could you please let me know how to do it? I am getting desperate at it!!

Many thanks in advance.
Best regards,


--
Valeria

I have tried:

Dim strTestVariable As String
Dim strCalculatedField As String

strTestVariable = "Volume (MT) Oct_2004"
strCalculatedField = "Weighted Average PM Deviation (by Volume)"

'Delete variable if it already exists
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").CalculatedF ields(test).Delete
On Error GoTo 0
ActiveSheet.PivotTables("PivotTable1").CalculatedF ields.Add
strCalculatedField, _
"=Deviation * Volume/" & strTestVariable, True


ActiveSheet.PivotTables("PivotTable1").PivotFields (strCalculatedField).Orientation = xlDataField
Range("B4").Select
With ActiveSheet.PivotTables("PivotTable1").DataPivotFi eld
.Orientation = xlColumnField
.Position = 1
End With

This should work. I believe the problem arises from using ', which should be
deleted:
Instead of
"='Deviation * Volume' /' Volume (MT) Oct_2004'"
try
"=Deviation * Volume / Volume (MT) Oct_2004"

Let me know if this works!

Martin

"Valeria" wrote:

Dear experts,
I am having again a problem with writing a code for a calculated field of a
pivot table.
The calculation of this field is performed with a variable, a string that
should represent the ' Volume (MT) Oct_2004', which is in the code below.
This is because I will change the database every month and there will be a
November, December, etc instead of October.

ActiveSheet.PivotTables("PivotTable1").CalculatedF ields.Add
"Weighted Average PM Deviation (by Volume)", _
"='Deviation * Volume' /' Volume (MT) Oct_2004'", True
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Weighted Average PM
Deviation (by Volume)").Orientation = _
xlDataField

If I now change the code with a test variable
test = " Volume (MT) Oct_2004"
this does not work at all, Excel does not recognize it. (error meassage "an
item name cannot be found", but I am sure the string is correct)

Could you please let me know how to do it? I am getting desperate at it!!

Many thanks in advance.
Best regards,


--
Valeria

Dear Martin,
I am still having problems. Excel won't accept the formula, it tells me
(depending on where I put the ') that either the item name does not exist, or
that Pivot table does not support the use of names, references, etc.
Is this latter the reason why I can't use my variable?

I have tried a lot of " ' " combinations, none of them, just on the ifrst
part of the formula, on the second... nothing works!

Many thanks,
best regards,
Valeria

"Martin Los" wrote:

I have tried:

Dim strTestVariable As String
Dim strCalculatedField As String

strTestVariable = "Volume (MT) Oct_2004"
strCalculatedField = "Weighted Average PM Deviation (by Volume)"

'Delete variable if it already exists
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").CalculatedF ields(test).Delete
On Error GoTo 0
ActiveSheet.PivotTables("PivotTable1").CalculatedF ields.Add
strCalculatedField, _
"=Deviation * Volume/" & strTestVariable, True


ActiveSheet.PivotTables("PivotTable1").PivotFields (strCalculatedField).Orientation = xlDataField
Range("B4").Select
With ActiveSheet.PivotTables("PivotTable1").DataPivotFi eld
.Orientation = xlColumnField
.Position = 1
End With

This should work. I believe the problem arises from using ', which should be
deleted:
Instead of
"='Deviation * Volume' /' Volume (MT) Oct_2004'"
try
"=Deviation * Volume / Volume (MT) Oct_2004"

Let me know if this works!

Martin

"Valeria" wrote:

Dear experts,
I am having again a problem with writing a code for a calculated field of a
pivot table.
The calculation of this field is performed with a variable, a string that
should represent the ' Volume (MT) Oct_2004', which is in the code below.
This is because I will change the database every month and there will be a
November, December, etc instead of October.

ActiveSheet.PivotTables("PivotTable1").CalculatedF ields.Add
"Weighted Average PM Deviation (by Volume)", _
"='Deviation * Volume' /' Volume (MT) Oct_2004'", True
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Weighted Average PM
Deviation (by Volume)").Orientation = _
xlDataField

If I now change the code with a test variable
test = " Volume (MT) Oct_2004"
this does not work at all, Excel does not recognize it. (error meassage "an
item name cannot be found", but I am sure the string is correct)

Could you please let me know how to do it? I am getting desperate at it!!

Many thanks in advance.
Best regards,


--
Valeria

I am using a Spanish version of Excel. Maybe you can send me the file
(reduced) with the names of the variables in the code. I can take a look at
the code and see if I can help you out if you want.


(remove nospam if you send me the file please in winzip format not exceeding
200Kb).

Martin

"Valeria" wrote:

Dear Martin,
I am still having problems. Excel won't accept the formula, it tells me
(depending on where I put the ') that either the item name does not exist, or
that Pivot table does not support the use of names, references, etc.
Is this latter the reason why I can't use my variable?

I have tried a lot of " ' " combinations, none of them, just on the ifrst
part of the formula, on the second... nothing works!

Many thanks,
best regards,
Valeria

"Martin Los" wrote:

I have tried:

Dim strTestVariable As String
Dim strCalculatedField As String

strTestVariable = "Volume (MT) Oct_2004"
strCalculatedField = "Weighted Average PM Deviation (by Volume)"

'Delete variable if it already exists
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").CalculatedF ields(test).Delete
On Error GoTo 0
ActiveSheet.PivotTables("PivotTable1").CalculatedF ields.Add
strCalculatedField, _
"=Deviation * Volume/" & strTestVariable, True


ActiveSheet.PivotTables("PivotTable1").PivotFields (strCalculatedField).Orientation = xlDataField
Range("B4").Select
With ActiveSheet.PivotTables("PivotTable1").DataPivotFi eld
.Orientation = xlColumnField
.Position = 1
End With

This should work. I believe the problem arises from using ', which should be
deleted:
Instead of
"='Deviation * Volume' /' Volume (MT) Oct_2004'"
try
"=Deviation * Volume / Volume (MT) Oct_2004"

Let me know if this works!

Martin

"Valeria" wrote:

Dear experts,
I am having again a problem with writing a code for a calculated field of a
pivot table.
The calculation of this field is performed with a variable, a string that
should represent the ' Volume (MT) Oct_2004', which is in the code below.
This is because I will change the database every month and there will be a
November, December, etc instead of October.

ActiveSheet.PivotTables("PivotTable1").CalculatedF ields.Add
"Weighted Average PM Deviation (by Volume)", _
"='Deviation * Volume' /' Volume (MT) Oct_2004'", True
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Weighted Average PM
Deviation (by Volume)").Orientation = _
xlDataField

If I now change the code with a test variable
test = " Volume (MT) Oct_2004"
this does not work at all, Excel does not recognize it. (error meassage "an
item name cannot be found", but I am sure the string is correct)

Could you please let me know how to do it? I am getting desperate at it!!

Many thanks in advance.
Best regards,


--
Valeria