How to update Getpivottable formula in Excel throught VBA

[raghavcacs]

Hi!

When I am trying to update the below formula in excel cell through VBA, it is throwing out an error

Range("B" & c).Text = "=(GETPIVOTDATA(" & " Actuals" & ",$A$6))/2"

but in the range given , the above formula is pasted without quotes for the word Actuals in excel which needs to be there as per the syntax of Getpivot table.

Can anybody help me how can i do it in Excel VBA?

 

[Norie]

Where is the error occurring?

On the worksheet or in the code?

 

[raghavcacs]

#name? error occurs in worksheet because in the sheet I am seeing a formula like the below:
=GETPIVOTDATA(Actuals,$A$6))/2

but it should be =
=GETPIVOTDATA("Actuals",$A$6))/2

Quotes before and after the word actuals can not be given in VBA.


<TABLE style="WIDTH: 131pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=174 border=0 x:str><COLGROUP><COL style="WIDTH: 131pt; mso-width-source: userset; mso-width-alt: 3181" width=174><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 131pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=174 height=17 x:err="#NAME?">

</TD></TR></TBODY></TABLE>

 

[Norie]

Quotes before and after the word actuals can not be given in VBA.

Eh, yes they can.

Range("B" & c).Text = "=GETPIVOTDATA(" & """Actuals""" & ",$A$6)/2"

 

[raghavcacs]

Hi Norie,

Thanks for the quick reply... It worked very well....

Regards

Raghavan