UPDATED WITH ANSWER: 我找到了问题的答案 . 我会在原始问题下方放置一个简化版本,只是为了方便起见,下面有一个更详细的版本作为接受的答案 .
我正在编写一个VBA函数来按照用户输入的日期范围过滤数据透视表 . 出于某种原因,它没有返回预期的结果 .
比较的日期有两种不同的日期格式 . 用户输入的日期采用mmmm yyyy格式(2013年10月) . 将此值拉入宏进行比较时,它将被正确转换为10/1/2013 . 数据透视表日期采用mmm yy格式(10月13日) . 当我使用代码 PivotItem.Value
调用此日期时,它似乎将日期转换为字符串"Oct 13."
我无法弄清楚宏正在做什么,因为它表现得有点不稳定 . 如果我在2011年10月至2013年10月期间运行它,它将返回从1月到9月的所有月份,每年,2008年,2009年,2010年等 . 如果我在2013年6月至2013年10月运行,则每年6月至9月返回 . 此外,在每个示例中,宏继续运行在数据透视表中的最大数据范围并获得错误 . 当我调试时,宏正试图将可见性设置为'true',以确定数据透视表中甚至不存在的日期(2014年1月的数据仅流经2013年10月的IE) . 不知道为什么会这样 .
下面是代码 . 任何见解将不胜感激 .
UPDATE:
所以问题肯定是日期格式 . 如果我将数据透视表中的字段设置更改为日期格式mm / dd / yyyy(10/1/2013),则宏的工作方式与预期完全相同 . 这将是对问题的简单修复,除了表格正在提供用户仪表板中看到的图表,我真的希望格式为mmm yy,因为它看起来更清晰 . 是否有一种简单的方法可以将格式转换为宏内的mm / dd / yyyy进行比较,然后在完成后返回所需的格式?
我仍然想了解为什么不同的日期格式返回这样的不同结果,当比较的原始数据是相同的,并且两者都被格式化为 dates ,而不是日期与文本或其他东西 .
Sub filterPivotDate(pt As PivotTable, strtDate As Date, endDate As Date)
Dim pf As PivotField
Dim pi As PivotItem
'Clear current date filter
Set pf = pt.PivotFields("Date")
pf.ClearAllFilters
'Set new date filter
For Each pi In pf.PivotItems
If pi.Value >= strtDate And pi.Value <= endDate Then
pi.Visible = True
Else
pi.Visible = False
End If
Next pi
end sub
ANSWER UPDATE: 我用以下代码行替换了我用来设置过滤器的循环:
pf.PivotFilters.Add Type:=xlDateBetween, Value1:=strtDate, Value2:=endDate
这解决了我使用日期格式的问题 . 以下接受的答案中的更多信息以及this website
3 回答
我可能错了,但你的一个<字符向后看 - 你不想要开始日期大于变量吗?
您可以先尝试转换字符串日期值:
我发现了一些关于通过VBA设置数据透视表过滤器的其他信息,这些信息改进了原始代码并解决了我遇到的问题 . 我决定发布它,因为我发现这些信息非常有用 .
您可以使用简单的一行命令设置许多不同的过滤器,而不是复杂的循环,这些循环可以解析所有数据并根据我之前的条件手动设置可见性 . 下面是更新的代码,它也解决了我在日期格式化方面遇到的问题 .
关于可以使用VBA设置的不同数据透视表过滤器设置,在this link to the globaliconnect website上有一些非常有用的信息 .
我仍然不确定为什么日期在循环中进行比较之前表现得很奇怪,但是日期有点像我猜...