0

I have a pivot table created from external data source(access) that contain number of records for each hour on a weekly basis for an average period of 3 months. Just beside this pivot table, I've created a column that calculates the average number record for each hour for a day. (A week here is just 5 days - sat & sun don't count).

To achieve the above, I have created a UDF that counts the number of weeks from the pivot data field (Week_Ending). To ensure the code don't count ghost or non-existent weeks, I have used the pivotcache refresh in the UDF. This works perfectly, except it now gives #value (A value used in the formula is the wrong data type) in the cells where I expect the daily average. I have found no issue with the cell formula and works if a single week is selected from the field "Week_Ending" instead of ALL.

I have attached the code and cell function and an image of the issue.

Screen Capture #value error

Cells formula in Cell E6 and it is similar upto cell E29 (Hourly cell reference is incremented by 1 for each cell)

=IF($E$4=1,GETPIVOTDATA("CountOfCase_Id",$A$4,"HOURLY",A6)/5,GETPIVOTDATA("CountOfCase_Id",$A$4,"HOURLY",A6)/($E$4*5))

Vba UDF function

Option Explicit

Function WeekCount(InputVal As Variant) As Integer
Dim book1 As String, PivotName As String

    book1 = ThisWorkbook.Name

    With Workbooks(book1).ActiveSheet
        If InputVal = "(All)" Then
            PivotName = .PivotTables(1).Name
            .PivotTables(PivotName).PivotCache.MissingItemsLimit = xlMissingItemsNone
            .PivotTables(PivotName).PivotCache.Refresh
            WeekCount = .PivotTables(PivotName).PivotFields("WEEK_ENDING").PivotItems.Count

        Else
            WeekCount = 1
        End If
    End With
End Function

I appreciate any help. Excel version is 2003.

SeanC
  • 15,695
  • 5
  • 45
  • 66
user2000380
  • 47
  • 1
  • 2
  • 8
  • Yes, the UDF is invoked from cell `E4 (=WeekCount(B2))`. If the formula as per your suggestion is copied down, the results are displayed. However, if change Week_Ending to a single week and back to ALL, then the same #Value error is displayed. My existing cell formula works if you manually input 13 or select a single week (UDF input 1 in cell E6). – user2000380 Jul 17 '13 at 03:34
  • WEEK_ENDING contain only date and time - `10/02/2013 12:00:00 AM`. However, the UDF does not do much except count the number of items when _ALL_ items are selected in the field Week_Ending. The issue started when I added code for Pivotcache refresh. – user2000380 Jul 17 '13 at 09:47
  • Yes, that did it. I put your suggested approach in place and it works like a charm. I used the `refreshtable` code and left out the `update` code. Update code bring back the same issue. Thanks. – user2000380 Jul 18 '13 at 05:08

1 Answers1

0

The problem turned out to be with

.PivotTables(PivotName).PivotCache.Refresh

in the UDF and not fixed with Application.Volatile. However @GSerg’s solution without p.update seems to have worked.

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139