CODEWITHSUNDEEP
vbaexcel
user
user

Reputation: 1351

Excel VBA Pivot Table -- Formatting Numbers as Percents without moving Decimal

I want to convert values in my Pivot Table to "0.0%" format without moving the decimal, e.g. 1 --> 1%, NOT 100%.

The code I'm using produces a "Run-time error '1004': Cannot change this part of a PivotTable report" error in my VBA pivot table macro. The code is:

Sub Cost_Pivot()
 NoOfOutputRows = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
 NoOfCols = Worksheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1!R1C1:R" & NoOfOutputRows & "C" & NoOfCols, Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet2!R1C1", TableName:="PivotTable1", DefaultVersion _
 :=xlPivotTableVersion12
 Sheets("Sheet2").Select
 Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Metric")
 .Orientation = xlRowField
 .Position = 1
 End With
 With ActiveSheet.PivotTables("PivotTable1").PivotFields("CAM")
 .Orientation = xlRowField
 .Position = 2
 End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("CAM")
 .Orientation = xlColumnField
 .Position = 1
 End With
 ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("Quantification"), "Sum of Quantification", xlSum
 Range("A2").Select
 ActiveSheet.PivotTables("PivotTable1").CompactLayoutRowHeader = "Metric"
 Range("B1").Select
 ActiveSheet.PivotTables("PivotTable1").CompactLayoutColumnHeader = "CAM"
ActiveSheet.PivotTables("PivotTable1").ColumnGrand = False
For Each ce In ActiveSheet.Range(Cells(1, 1), Cells(100, 100))
If IsNumeric(ce.Value) = True Then
ce.Value = ce.Value * 0.01
End If
Next ce
 With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Quantification")
 .NumberFormat = "0.0%"
 End With
End Sub

The error appearing is at the ce.Value = ce.Value * .01 where I want to multiply the cell values by .01 before converting them to a percent number format. I read this article saying that I have to create a calculated value, but the article isn't VBA related and I don't know how to create a calculated value in a macro.

Is there a better solution?

Upvotes: 0

Views: 5129

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Instead of trying to alter the cell's .Value, perhaps just adjust the .NumberFormat, like:

ce.NumberFormat = "General\%"

This way, you don't need to do the multiplication. This is a custom number format that will render 1 like 1% without affecting the underlying data.

Upvotes: 3

Related Questions