多维数据集的pivotFields visibleitemslist

本文介绍了多维数据集的pivotFields visibleitemslist的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

嗨 我想问一下枢轴报告。 scenrio是，我在枢轴报告中设置了过滤列，不幸的是列也包含空值，我需要为立方体工作以删除该空值，问题是具有该空值，在列中执行VBA写在背景崩溃的脚本？我正面临错误，应用程序定义或对象定义错误代码：1004，当我试图迭代pivotfields.visibleitemslist。我怀疑在列中有空字段时发生错误。请给我任何建议。我的代码如下

Hi i would like to ask about pivot report. The scenrio is, i have set filter column in pivot report, unfortunately the column contains a null value as well, and i need to work for cube to remove that null value, the question is that having that null value, in column does the VBA Script written in background crashes? i am facing the error,''Application Defined or object defined Error'' code:1004, when i am trying to iterate pivotfields.visibleitemslist. and i am doubtfull that error is occuring having null field in column. Any suggestions please. my code is as follows

pType = Sheet1.Cells(25,"B") If pType = "(Multiple Items)" Then Dim pt As PivotTable Dim payTypeField As PivotField Dim items As Variant Dim Hitems As Variant Dim str As String Dim ptables As PivotTable Set pt = Sheet1.PivotTables("PivotTable3") pType = "" Set payTypeField = pt.PivotFields("[DimPayment].[Payment-TypeName]") For Each items In payTypeField.VisibleItemsList pType = pType + Replace(Mid(CStr(items), InStr(CStr(items), "&") + 2), "]",",") Next items pType = Mid(pType, 1, Len(pType) - 1) End If pType = "'"& pType & "'"

推荐答案

替换： Replace: pType = Sheet1.Cells(25,"B")

with ：

with:

pType = Sheet1.Cells(25,2) 'or 'pType = Sheet1.Range("B25")

更多： 细胞收集 [ ^ ] 单元格对象 [ ^ ] 范围对象 [ ^ ] VBA to Read&修改Excel中的数据透视表 [ ^ ] 数据透视表对象[Excel 2003 VBA语言参考] [ ^ ]

More: Cells Collection[^] Cell Object[^] Range Object[^] VBA to Read & Modify a Pivot Table in Excel[^] PivotTable Object [Excel 2003 VBA Language Reference][^]