使用标题名称的自动筛选字段 [英] Autofilter Field using the header Name
问题描述
我尝试使用自动过滤器功能,但使用标题名称为"ID"的字段而不是列数.我已尽力而为,并收到以下错误消息:高级筛选器在迭代中失败,运行时错误1004"
i tried to use the Autofilter function but using field with the header name "ID" instead the number of column. i tried my best and i got this error message "Advanced filter fails at iterations, run-time error 1004"
我被困了两天.谢谢您的帮助
I am stuck for two days with it . thnk's for your help
代码:
Sub AdataPreparation()
Dim WorkBk As Workbook, WorkSh As Worksheet, WrkTab As range, FilterRow As Variant
Set WorkBk = Workbooks.Open(Filename:="C:\Users\Documents\DataApplied.xlsm")
Set WorkSh = Sheets("sheet2")
WorkSh.Activate
Set WrkTab = range("A1").CurrentRegion
WrkTab = ActiveRange
FilterRow = Application.Match("ID", WrkTab, 0)
Selection.AutoFilter Field:=FilterRow, Criteria1:="="
End Sub
推荐答案
问题可能在于没有为 .Autofilter
选择要过滤的任何内容.尝试用范围或 .UsedRange
替换 Selection
.
The issue probably lies in not having anything selected for your .Autofilter
to filter. Try replacing Selection
with a range, or the .UsedRange
.
您也不需要 WrkTab
,我看不到它有任何用途-在这里我使用 .Find
:
You also don't need WrkTab
, I don't see it having any purpose - here I use .Find
instead:
Sub AdataPreparation()
Dim WorkBk As Workbook, WorkSh As Worksheet, FilterRow As Variant
Set WorkBk = Workbooks.Open(Filename:="C:\Users\Documents\DataApplied.xlsm")
Set WorkSh = Sheets("sheet2")
WorkSh.Activate
FilterRow = Rows("1:1").Find(What:="ID", LookAt:=xlWhole).Column
WorkSh.UsedRange.AutoFilter Field:=FilterRow, Criteria1:="="
End Sub
我应该补充一点,最好是显式引用您的范围,而不是使用 UsedRange
I should add that it would be best for you to explicitly refer to your range instead of using UsedRange
这篇关于使用标题名称的自动筛选字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!