Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Syntax
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Syntax
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" & IndxNumber & "')/ Total" & IndxNumber Thanks, Greg -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Syntax
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. -- Regards, Tom Ogilvy "Todd Huttenstine" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Syntax
Ok I got it to work after I restarted. My computer here
at work has these problems from time to time. -----Original Message----- 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula syntax | Excel Discussion (Misc queries) | |||
Need help with formula syntax | Excel Discussion (Misc queries) | |||
formula syntax. | Excel Discussion (Misc queries) | |||
Formula syntax {;;;} | Excel Worksheet Functions | |||
Formula Syntax | Excel Programming |