getpivotdata in vba - error handling

[ankita.sethi]

Hi,

I am writing a code in VBA using getpivotdata function. It is working fine untill the value does not exist.

Dim pt As PivotTable
Sheets("Pivot2").Select
Set pt = ActiveSheet.PivotTables(1)

Cells(2,1).Value = pt.GetPivotData("ID", "GCB", "1", "Gender", "F", "9 box Rating_2012", NBArr(k)).Value
'the code is looping through the values in an array called NBArr


I have tried to handle this as follows:

If IsError(pt.GetPivotData("ID", "GCB", GCB1, "Gender", Gen1, "9 box Rating_2012", NBArr(k)).Value) = True Then
Cells(2,1).Value= "0"

ElseIf IsError(pt.GetPivotData("ID", "GCB", GCB1, "Gender", Gen1, "9 box Rating_2012", NBArr(k)).Value) = False Then

Cells(2,1).Value = pt.GetPivotData("ID", "GCB", GCB1, "Gender", Gen1, "9 box Rating_2012", NBArr(k)).Value
End If


However, even this is not working (in case when the function finds no value). Please help?

 

[Andrew Poulsom]

You could try:

On Error Resume Next