pivot tables long string formula for calculated field
 

I am having a problem with my VBA code.
I have written code to create a pivot table that consists of a contacanated
field comq14b. This field has items of each possible outcome for a question
(Q14b) of a possible 18 precodes. eg. 1000000000000000000000000000=precode1
and 1200000000000000000000000000=precode1 & 2. length of each item name will
be 28 characters.

I can't group because as the 2nd example above, it will need to be in
precode 1 calculated item & precode 2 calculated items.
I don't want to use the multiple consolidation pivot table because I have 4
filtered fields that need to go in the page area and it didn't allow me in
the multiple consolidation table to add those in. Unless there is a way I am
not aware of, this would be the ideal option to use.

However, I decided to create calculated items for each of my precodes and
use a formula to define that item based on the numeric item names decribed
above.

my code is -

ActiveSheet.PivotTables("PivotTable2").PivotFields ("combq14b").CalculatedItems. _
Add precode, ciform, True

ciform is my formula (is adding all the possibilities for precode 1 and
dividing by the count of all items
eg.

=('10000000000000000000000000000'+'100006000000000 0000000000000'
.....etc)/COUNT('1000000000000000000000000000','100006000000 0000000000000000'
.... etc)

This string can get up to 2000 characters long. I tested it with the first
part upto the "/count" part and for some where the length was less than 255
it worked. So I know its a length issue.

I tried splitting the string ciform into 7 parts to array formval (declared
as variant) ie. formval(1)=characters 1-250 and formval(2)=characters 251-500
etc. but I can't reference them as formval in the calculated items add
definition.

I wonder if anyone can help me. I am getting fed up of looking at it!

Regards
Jo