Replacing code in text with variables

[Rowanhf12]

Hi

I have the following code

    Dim Ch1 As Excel.Range
    Set Ch1 = Range("J11")

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Average of Cost").Function = " & Ch1 & "

The variable in cell J11 will be Average, but the section of the code " & Ch1 & " should read xlAverage.
I'm getting the run-time error 1004 at the moment.
I want this to be a variable
(I know that if they change the Average to Sum etc, then the piece of code that says Average of Cost should read Sum of Cost etc)

Any help???

 

[BobUmlas]

You can't do what you want. It's as if you wanted to change
If X=5 Then...
to
Strg1="If "
Strg1 X=5 Then...

It just doesn't work that way. You can do something like this
If Ch1="xlAverage" then ActiveSheet.PivotTables("PivotTable1").PivotFields("Average of Cost").Function = xlAverage
and may be nested ifs or Case statements to handle the others like xlSum, etc.

 

[Rowanhf12]

Ahh, well that's annoying. But thank you for clearing that up.

Just in case anyone has the same problem in the future and stumbles upon this thread.
Below is the code I used:

    If InStr(1, ActiveSheet.Range("I11").Value, "Average") > 0 Then
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Average of Cost").Function = xlAverage
    End If

and then repeat the 'If' statements for the other possible outcomes in Pivot - sum, count etc