Pivot table Count/Sum

[Sigh]

Whenever I create a Pivot Table I inevitably have to change the calculations in the table from Count to Sum, is there a way to program it so that it defaults to Sum?

 

[Worf]

You probably have blank cells, errors or text values. Try the following clean up code on a copy of your data:

Sub Convert_to_Number()
Dim r As Range, cell As Range
Set r = [b131:b145]                         ' range of interest
For Each cell In r
    If IsError(cell) Then cell = 0
    If Not WorksheetFunction.IsFormula(cell) Then cell = Replace(cell, """", "")
    If WorksheetFunction.IsLogical(cell) Then cell = Abs(cell)
    If WorksheetFunction.IsText(cell) Then
        On Error Resume Next
        cell = CDbl(cell)
        If Err.Number <> 0 Then cell = 0    ' this text is not a number
        Err.Clear
        On Error GoTo 0
    End If
    If Len(cell) = 0 Then cell = 0          ' empty cell
Next
r.NumberFormat = "0.0"
End Sub