vba 通过在单元格中输入值来更改数据透视表过滤器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18774793/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Changing a PivotTable filter by entering a value into a cell
提问by user2774361
I have looked around for an answer and in fact the code that I am using is from this site. I have the VBA for changing PivotTablefilters by inputing a value into a seperate cell like so:
我四处寻找答案，实际上我使用的代码来自此站点。我有 VBA 用于PivotTable通过将一个值输入到一个单独的单元格来更改过滤器，如下所示：
Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Sheets("Dashboard").Range("Lane1")) Is Nothing Then
Sheets("Dashboard").PivotTables("PivotTable1").PivotFields("TLEG"). _
ClearAllFilters
Sheets("Dashboard").PivotTables("PivotTable1").PivotFields("TLEG").CurrentPage _
= Sheets("Dashboard").Range("Lane1").Value
End If
End SUb
The code works fine. It lets me enter the value and filters accordingly, but when I delete the value in the cell it does not filter "all". Instead, the problem I run into is that a runtime error '1004' is thrown when I delete the value in the cell. I hit end and it gives me the answer. However the error keeps popping up.
该代码工作正常。它让我输入值并相应地过滤，但是当我删除单元格中的值时，它不会过滤“全部”。相反，我遇到的问题是当我删除单元格中的值时会抛出运行时错误“1004”。我结束了，它给了我答案。但是错误不断弹出。
I am pretty much a newborn when it comes VBA, so it might be so that I have missed something glaringly obvious.
当谈到 VBA 时，我几乎是一个新生儿，所以我可能错过了一些非常明显的东西。
回答by Tim Williams
Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng=Me.Range("Lane1")
If Not Application.Intersect(Target, rng) Is Nothing Then
With Sheets("Dashboard").PivotTables("PivotTable1").PivotFields("TLEG")
.ClearAllFilters
If Len(rng.Value)>0 Then .CurrentPage = rng.Value
End With
End If
End SUb
