StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPivotCache Refresh - Cell formula return #Value
text
Body
copied!<p>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).</p> <p>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.</p> <p>I have attached the code and cell function and an image of the issue.</p> <p><img src="https://i.stack.imgur.com/sxtyv.jpg" alt="Screen Capture #value error"></p> <p>Cells formula in Cell E6 and it is similar upto cell E29 (Hourly cell reference is incremented by 1 for each cell)</p> <pre><code>=IF($E$4=1,GETPIVOTDATA("CountOfCase_Id",$A$4,"HOURLY",A6)/5,GETPIVOTDATA("CountOfCase_Id",$A$4,"HOURLY",A6)/($E$4*5)) </code></pre> <p>Vba UDF function</p> <pre><code>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 </code></pre> <p>I appreciate any help. Excel version is 2003. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload