CODEWITHSUNDEEP
excelpivot-tablevba
ofir
ofir

Reputation: 41

How can I change the format value of the cells in a pivot table?

I am creating a pivot table in excel sheet by using vba code. I want the values in the pivot to be formatted as scientific with a symbol E,plus/minus sign and 2 decimal places. e.g: 2.28E-10, 1.1E-09, 25E-3.

How do i reference cells within the Pivot table? or shuold i reference them in the data source?

(my code takes data from sheet1 and creates another sheet named pivot, then creates the pivot)

so far:

Set rng = Range("b7:q9")     ' 

For Each cell In rng
    .Orientation = xlDataField
    .Position = 1
    .Function = xlAverage
     cell.NumberFormat = "#,##0" & "E-3"
Next cell

It does not work properly.

or maybe this one:

Dim pvt As PivotTable
Dim pff As PivotField
Dim pi  As PivotItem

With pff
    For Each pi In pff.PivotItems              
        pi.DataRange.NumberFormat = " 0.00#" & "E-3"               
        'pvt.DataBodyRange.NumberFormat = "0.00#"
    Next pi
End With

Please suggest how to do this with vba.

Any help will be much appreciated!

Upvotes: 1

Views: 1076

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

If you mean to format all the values inside the PivotTable (I hope I understand what you meant to do), then you need to reference the values inside the PivotTable by using pvt.DataBodyRange.

Try the code below:

pvt.DataBodyRange.NumberFormat = " 0.00#" & "E-3"

Edit 1: to loop through the Pivot-Table's DataBodyRange cell by cell:

Dim PvtRng As Range, C As Range

Set PvtRng = pvt.DataBodyRange ' <-- set the Pivot Data range

For Each C In PvtRng ' loop cell by cell inside the Pivot-Table's DataBodyRange
    C.NumberFormat = " 0.00#" & "E-3"
Next C

Upvotes: 1

Related Questions