按单元格值筛选数据透视表，错误1004 VBA [英] Filtering Pivot table by cell values, error 1004 VBA

问题描述

我有一个数据透视表，我想根据其中在2个单元格中输入的开始日期和结束日期来过滤发票/发货日期.我有以下代码，但始终收到错误"1004-应用程序定义或对象定义的错误"

I have a pivot table where I would like to filter the invoice/despatch date based on a start and end date entered in 2 cells. I have the following code but keep getting an error "1004 - Application Defined or Object Defined error"

请帮助..我正在使用Excel 2016

please help .. I'm using Excel 2016

Dim Invoice_Start_Date As Date
Dim Invoice_End_Date As Date
Invoice_Start_Date = CDate(Worksheets("Despatch Template").Cells(17, "F").Value)
Invoice_End_Date = CDate(Worksheets("Despatch Template").Cells(17, "G").Value)
Sheets("Despatch Template").Select

MsgBox IsDate(Invoice_End_Date)
MsgBox IsDate(Invoice_Start_Date)

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable1").PivotFields("DESPATCH 
DATE").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("DESPATCH 
DATE").PivotFilters.Add2 _
Type:=xlDateBetween, Value1:=CLng(Invoice_Start_Date), 
Value2:=CLng(Invoice_End_Date)

msgbox仅在测试日期时返回TRUE.

the msgbox returns TRUE just for testing dates.

我已经尝试了几乎可以在网上找到的所有内容，但无法正常工作. 谢谢

I've tried just about everything I can find online, but it wont work. thanks

C

推荐答案

我找到了答案.这是由于源数据在某些单元格中具有非日期值.工作正常.

I found the answer.. it was due to the source data having non date values in some of the cells.. once I changed these for actual dates the code worked correctly.

这篇关于按单元格值筛选数据透视表，错误1004 VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！