PDA

View Full Version : VBA Pivot Calcfield



cpremesh
07-11-2018, 09:38 AM
Team

Am trying to create a dynamic Pivot with calculated field.
Please note the below details.

Row Labels Sum of Revenue
Caption $0.00
A $0.00
B $0.00
Grand Total $0.00

"Caption" is a row field and "Revenue" is calculated field.
I want to calculate the "Revenue" based on the "Caption" or "Caption" values available in range
if Caption = "A" then
Revenue= Revenue*75/100
else if Caption = "B"
Revenue= Revenue*50/100
end if

I did the below option while adding the calculated field (Revenue)
.CalculatedFields.Add Name:=sCalcFieldName, Formula:="=IF( " & .PivotFields("Caption") & "='A', " & sRevenue & "* 75/100)"

but not getting the exact value.

Please help