Formula Syntax

[Todd Huttenstine]

Dim IndxNumber as Long
IndxNumber = ComboBox1.ListIndex + 1

With Worksheets("Reps Current").PivotTables("PivotTable1")
..CalculatedFields.Add "testfieldname", "= (Total28- 'On
Time28')/ Total28"
..PivotFields("testfieldname").Orientation = xlDataField
..PivotFields("Sum of testfieldname").Caption = "Percent
Overdue"
..PivotFields("Percent Overdue").NumberFormat = "0.00%"
End With

Above is a code that creates a calculated field and
inserts it into PivotTable1. The .calculatedfields line
is the formula of the calculatedfield. This code works
fine but instead of specifiying the eaxct pivotfields that
make up the calculatedfield formula, I need for it to
reference the variable IndxNumber. In this example
variable IndxNumber is currently 31. So in this case
instead of the formula being: "= (Total28- 'On Time28')/
Total28"
it needs to say "= (Total31- 'On Time31')/ Total31"

I have tried "= (Total" & IndxNumber & "- 'On Time" &
IndxNumber & "')/ Total" & IndxNumber
but I keep getting an error.

How do I write this formula so it functions like I need?


Thank you
Todd Huttenstine

 

[Gjones]

Hi Todd;

I have had this happen on sorting and charting. Try
either of these two things.

(1) change the declariation for IndxNumber to be a
varriant. It probably won't work but it might.

(2) Construct the entire string demonstrated below at a
variable and then pass it as a whole.

"= (Total" & IndxNumber & "- 'On Time" &

IndxNumber & "')/ Total" & IndxNumber

would become

varTodd = (Total" & IndxNumber & "- 'On Time" &

 

[Tom Ogilvy]

No reason it shouldn't work if the hardcoded code works.

Possibly your resulting formula isn't legal - IndxNumber isn't what you
expect it to be as an example.

 

[Todd Huttenstine]

Ok I got it to work after I restarted. My computer here
at work has these problems from time to time.