[RESOLVED] [Excel 2003] Pivot Table GetData problem

[Torc]

My Pivot Table contains a number of Rows which are Regions, and a number
of Columns which are Types.

At first I noticed that I had a problem if I was trying to get the value for a
Region or Type that was not included in the Pivot Table it crashed, so I came
up with the following to return 0 if the Row or COlumn heading was not
found, which works fine.

However I have now encountered a new problem, if the Row and Column
headings are there, but the spreadsheet halts with the following error:

Run-Time error '1004'
Application-defined or object-defined error.

when it tries to retrieve a value in the GetData method, if there is no value for
that Row and Column.

Any suggestions as to why and how to get around this.

ResSht is a Worksheet variable.

Function Pivot(ByVal RowField As String, ByVal ColField As String) As Long
    Pivot = 0
    If InPivotRow(RowField) And InPivotCol(ColField) Then
        Pivot = ResSht.PivotTables(PivotTableName).GetData("'Count of Region' '" & _
                    RowField & "' '" & ColField & "'")
    End If
End Function
Function InPivotRow(ByVal FieldName As String) As Boolean
' True if FieldName in Row Fields
Dim Count As Long
Dim PvtField As PivotField
    InPivotRow = False
    For Each PvtField In ResSht.PivotTables(PivotTableName).RowFields
        For Count = 1 To PvtField.PivotItems.Count
            If PvtField.PivotItems(Count).Name = FieldName Then
                InPivotRow = True
                Exit Function
            End If
        Next Count
    Next PvtField
End Function
Function InPivotCol(ByVal FieldName As String) As Boolean
' True if FieldName in Col Fields
Dim Count As Long
Dim PvtField As PivotField
    InPivotCol = False
    For Each PvtField In ResSht.PivotTables(PivotTableName).ColumnFields
        For Count = 1 To PvtField.PivotItems.Count
            If PvtField.PivotItems(Count).Name = FieldName Then
                InPivotCol = True
                Exit Function
            End If
        Next Count
    Next PvtField
End Function

[Torc]

I managed to work out how to deal with this. although the code is
quite a mess , still it works.

I grab the first four letters of the TableRange1 Address of the Pivot Table,
then set TheCell to this, offset by the number of the RowField row and the
number of the ColField Column.

Then I just need to check to see if the value is Numeric and return it, else
return 0.

Private Function Pivotal(ByVal RowField As String, ByVal ColField As String) As Long
Dim vv As String
Dim TheCell As Range
Dim Row as long
Dim Col as long
    Pivotal = 0
    vv = Left(ResSht.PivotTables(PivotTableName).TableRange1.Address, 4)
    Row = CountPivotRow(RowField)
    Col =  CountPivotCol(ColField)
    If (Col = -1) or (Row = -1) Then
        Exit Function ' Returning 0
    End If
    Set TheCell = ResSht.Range(vv).Offset(Row + 1, Col)
    If IsNumeric(TheCell.Value) Then
        Pivotal = TheCell.Value
    End If
    Set TheCell = Nothing
End Function
Private Function CountPivotRow(ByVal FieldName As String) As Long
' returns number of row, -1 if not found
Dim Count As Long
Dim PvtField As PivotField
    For Each PvtField In ResSht.PivotTables(PivotTableName).RowFields
        For Count = 1 To PvtField.PivotItems.Count
            If PvtField.PivotItems(Count).Name = FieldName Then
                CountPivotRow = Count
                Exit Function
            End If
        Next Count
    Next PvtField
    CountPivotRow = -1
End Function
Private Function CountPivotCol(ByVal FieldName As String) As Long
' Returns number of Column, -1 if not found
Dim Count As Long
Dim PvtField As PivotField
    For Each PvtField In ResSht.PivotTables(PivotTableName).ColumnFields
        For Count = 1 To PvtField.PivotItems.Count
            If PvtField.PivotItems(Count).Name = FieldName Then
                CountPivotCol = Count
                Exit Function
            End If
        Next Count
    Next PvtField
    CountPivotCol = -1
End Function