Pivot tables: Formulas, references, unsupported?

[xvexed]

Hi,

I'm attempting to clean up some code to create a pivot table. From 25 columns of $, 25 columns of Units, I am trying to create 25 columns of Average Price. Apparently pivot tables don't support arrays, and references according to the error message I get but then I rarely give up without at least running through this board. Thanks. All suggestions welcome

This is the line I'll have to write 25x:
ActiveSheet.PivotTables("PT1").CalculatedFields.Add "ARP", "= 'I$1'/ IU1"

that I would love to get into some code like:
For x = 1 To 25
dollarname = "I$" & x
unitname = "IU" & x
calcvar = "= " & dollarname & "/" & unitname

ActiveSheet.PivotTables("PT1").CalculatedFields.Add "myname", calcvar

Next x

''''''''''''''''''''''''
here's the whole thing clean up a bit, the $ and U portion works fine, it's the Average Price at the end I'm stuck on:

Sub createpivot()

'pivotarea
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"DATA!R1C1:R10C85", TableDestination:="", TableName:="PT1"

ActiveSheet.PivotTableWizard TableDestination:="R1C1:R10C85"

ActiveSheet.PivotTables("PT1").AddFields RowFields:=Array("UPC", "Data")

Dim ColumnArray As Variant

ColumnArray = Array("UPC", "ITEM", "BRAND", "CONTENT", _
"SUBCATEGORY", "CATEGORY")

ActiveSheet.PivotTables("PT1").AddFields PageFields:=ColumnArray


'dollars
For x = 1 To 26
myfield = "I$" & x
myname = "$" & x
With ActiveSheet.PivotTables("PT1").PivotFields(myfield)
.Orientation = xlDataField
.Name = myname
.NumberFormat = "$#,##0"
.Function = xlSum
End With
Next x

'units, no problem

For x = 1 To 26
myfield = "IU" & x
myname = "U" & 6 x
With ActiveSheet.PivotTables("PT1").PivotFields(myfield)
.Orientation = xlDataField
.Name = myname
.NumberFormat = "#,##0"
.Function = xlSum
End With
Next x

'Average Price
For x = 1 To 26

myname = "ARP" & x
dollarname = "I$" & x
unitname = "IU" & x
calcvar = "= " & dollarname & "/" & unitname

ActiveSheet.PivotTables("PT1").CalculatedFields.Add "myname", calcvar
ActiveSheet.PivotTables("PT1").PivotFields(myname).Orientation = xlDataField

Next x

End Sub

 

[Mark W.]

What are the field names in your data list for units and dollars?

 

[xvexed]

Mark thanks for responding however I figured it out... I guess. For some reason things worked find if i created the calculated field first setting it equal to zero and then going back and adding the formula.

As for your question the Dollars where in columns named 'I$25:I$0' and units IU25:IU0. These were renamed into field names '$25:$0' and U25:U0.

Thanks


For x = 1 To 25
myfield = "AP" & x
myname = "ARP" & x

ActiveSheet.PivotTables("PT1").CalculatedFields.Add myfield, "= 0"
With ActiveSheet.PivotTables("PT1").PivotFields(myfield)
.Orientation = xlDataField
.Name = myname
.NumberFormat = "#,##0.00"
End With
Next x

' arp formula

For x = 1 To 25

myname = "AP" & x
dollarname = "I$" & x
unitname = "IU" & x
calcvar = "= '" & dollarname & "'/ " & unitname

With ActiveSheet.PivotTables("PT1")
.CalculatedFields(myname).Formula = calcvar
End With

Next x
This message was edited by xvexed on 2002-09-10 14:25

 

[Mark W.]

I think you misunderstood my question. If your data list's column labels were 'Dollars' and 'Units' why would you use?...

ActiveSheet.PivotTables("PT1").CalculatedFields.Add myfield, "=Dollars/Units"