多维数据集的pivotFields visibleitemslist [英] population of pivotFields visibleitemslist from cube

问题描述

嗨



我想问一下枢轴报告。 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][^]

这篇关于多维数据集的pivotFields visibleitemslist的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！