自动筛选器在筛选视图中看不到数值数据(多个值) [英] AutoFilter NOT seeing numerical data in Filtered view (Multiple Values)
问题描述
在这段自动过滤代码上工作了一段时间.就目前而言，它运作良好.如果我在报价"中使用搜索条件替换FilterCriteria，则每次都可以使用.但是，当尝试在FilterCriteria中传递数字时，它每次都找不到我范围内的任何内容(仅A:D！).可以找到Colums E:G中的所有文本字段，因为它们都是文本. A:D列不返回任何内容.我尝试将A:D格式化为文本而不是数字，并且在过滤时仍然看不到任何内容.样本范围有望在最后显示.
Been working on this AutoFiltering code for a while. It works well as far as it goes. If I use my search criteria in "Quotes" replacing FilterCriteria it works everytime. However, when trying to pass numbers along in FilterCriteria it fails to find anything in my range (A:D ONLY!) everytime. It finds all text fields in Colums E:G fine as they are all text. Columns A:D returns nothing. I tried formatting A:D as text instead of numbers and it STILL sees nothing when filtering. Sample range shown hopefully at end.
Sub FindProduct()
'Note: This macro uses the function LastRow at end of Module
' Highly moded code from Ron de Bruin
'To define My_Range
Dim My_Range As Range
Dim CalcMode As Long
Dim ViewMode As Long
Dim CCount As Long
'To define New Sheet and Range
Dim WSNew As Worksheet
'Use for column and filter data selection
Dim FilterCriteria As String
Dim PickCol As String
'Set filter range on ActiveSheet
Set My_Range = Range("A1:G" & LastRow(ActiveSheet))
My_Range.Parent.Select
' ************************************
My_Range.Parent.AutoFilterMode = False
' Unprotect sheet, turn off AutoFilter, Show All
With ActiveSheet
.Unprotect
On Error Resume Next
.ShowAllData
End With
' Code to check if workbook is protected here. Redundant.
' ****************************************
'Turn off ScreenUpdating, Calculation, EnableEvents code here
' +++++++++++++++++++++++++++++++++++
' Use this to pick a Column to search and your FilterCriteria
PickCol = InputBox("What Column do you want to search in " & vbCrLf _
& "(A=1,B=2,C=3,D=4,E=5,F=6,G=7)?" _
& vbCrLf & vbCrLf, "Select Column to Search")
' Input error check
' ######################
FilterCriteria = InputBox("What are you looking for?" _
& vbCrLf & vbCrLf & "This will work with partial Information.", _
"Enter Filter Parameter")
' Input error check
' *********************************************************
' Insert PickCol and FilterCriteria variables
My_Range.AutoFilter Field:=PickCol, Criteria1:="=*" & FilterCriteria & "*"
'Check if there are not more then 8192 areas (limit of areas that Excel can copy)
CCount = 0
On Error Resume Next
CCount = My_Range.Columns(1).SpecialCells(xlCellTypeVisible).Areas(1).Cells.Count
On Error GoTo 0
If CCount = 0 Then
MsgBox "There are more than 8192 areas:" _
& vbCrLf & "It is not possible to copy the visible data."
Else
' ***********************************************
'Delete "Filtered Data" sheet if it exists code here
' ***********************************************
' ------------------------------
'Add a new Worksheet
Set WSNew = Worksheets.Add(After:=Sheets(ActiveSheet.Index))
On Error Resume Next
WSNew.Name = "Filtered Data"
' ------------------------------
' ///////////////////////////////////////////////////
'Copy/paste the visible data to the new worksheet
My_Range.Parent.AutoFilter.Range.Copy
' Paste copied range starting at Cell("A2")
With WSNew.Range("A2")
.PasteSpecial Paste:=8
.PasteSpecial xlPasteAll
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
' ///////////////////////////////////////////////////
' *****************************************
'Adds Formatted Text to Cell ("A1") code here
' *****************************************
End If
' Turn off AutoFilter
My_Range.Parent.AutoFilterMode = False
' ******************************************************
'More finishing code here
' ******************************************************
End Sub
Function LastRow(Sh As Worksheet)
On Error Resume Next
LastRow = Sh.Cells.Find(What:="*", _
After:=Sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
样本数据:
A B C D E F G
Date Rvd Qty File# P.O.# Cust Name Vend Name Carrier
02/14/15 210 41680 38565 Some Tech John DHL
03/08/15 458 17017 38569 Them Guys Donn Fedx
03/12/15 350 16736 38541 Some Guys Teri UPS
03/24/15 236 42630 38655 Some Tech John DHL
04/08/15 458 56985 85693 Them Guys Donn Fedx
04/12/15 350 12345 43851 Some Guys Teri UPS
04/18/15 838 56685 85693 Them Guys Donn Fedx
05/05/15 110 13245 43851 Some Guys Teri UPS
无论出于何种原因，当它使用A:D的任何数字运行自动筛选时，都无法提供任何已过滤的数据.我很沮丧，因为如果我将所需的确切值放在自动过滤器"行中，它将返回经过过滤的数据.
For whatever reason when it runs the AutoFilter using any numbers for A:D it fails to give any filtered Data. I'm stumped as I said It WILL return filtered data IF I place the exact value I want in the AutoFilter line.
可以肯定这行是我的问题: My_Range.AutoFilter字段:= PickCol，条件1:="= "&筛选标准""
Pretty sure this line is my issue/ problem: My_Range.AutoFilter Field:=PickCol, Criteria1:="=" & FilterCriteria & ""
有什么想法吗?
我想现在我必须弄清楚如何真正完成这项工作.在工作表上正确使用自动过滤器可以正常工作.如果我必须按照我认为本文所显示的去做，那么我必须再添加4列，并且必须在生成此列表的表单上重写SaveLog代码中的代码.听起来我需要为所有内容大幅增加代码的大小.对于像我这样的新手，我现在肯定不知所措.
I guess now I have to figure out how to actually make that work. Using Autofilter properly on the sheet it works fine. If I have to do as I think the article shows then I have to add 4 more columns AND I have to rewrite the code in the SaveLog Code on the form that generates this list. Sounds like I need to substantially increase the size of my code for everything. For a Novice as myself I'm certainly overwhelmed at this point.
推荐答案
这不是很漂亮，我敢肯定这段代码中还有很多改进的余地，但这是我要使用的.我将"FilterCriteria"变暗为Variant，并在"PickCol"和"FilterCriteria" InputBox之后紧接着的单行替换为以下Select-Case例程:
It isn't pretty, I'm sure there's tons of room for improvements in this code but here is what I am going to use. I Dimmed "FilterCriteria" as Variant and replaced the single line immediately following the "PickCol" and "FilterCriteria" InputBoxes with the following Select - Case routine:
' ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
' Determines whether "FilterCriteria" is Date, Numerical or text input
Select Case PickCol
Case 1
' "PickCol" - Column "A" (1) is Date
' Define for Numeric
My_Range.AutoFilter Field:=PickCol, Criteria1:=FilterCriteria
Case 2 To 4
' "PickCol" - Column "B:D" (2-4) Are Numerical
' Define for Numeric
My_Range.AutoFilter Field:=PickCol, Criteria1:=FilterCriteria
' This column CAN have mixed Numeric and Text data. So-> Evaluate that
If IsNumeric(FilterCriteria) Then
' Define for Numeric
My_Range.AutoFilter Field:=PickCol, Criteria1:=FilterCriteria
Else
' Redefine for Text instead
My_Range.AutoFilter Field:=PickCol, Criteria1:="=*" & FilterCriteria & "*"
End If
Case Else
' "PickCol" - Column "E:G" (5-7) Are Text
' Define for Text
My_Range.AutoFilter Field:=PickCol, Criteria1:="=*" & FilterCriteria & "*"
End Select
' ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
就像我说的那样. 它可能并不漂亮，但确实可以工作."
Like I said. "It might not be pretty, but it does work."
感谢@Byron的建议
Thanks for the advice @Byron
这确实允许在E:G中输入通配符(部分)，尽管A:D必须完全按照书面规定.对于输入的不存在的数据，我仍然必须捕获错误，否则它会崩溃.与其余步骤相比，这只是一小步.在工作中又给我放了一块饼干.肚子饿也是一件好事.哈哈
This does allow for wildcards (partial) input in E:G although A:D MUST be exactly as written. I still have to catch errors for non-existent data entered or it crashes. That is one small step compared to the rest of this. Whew another cookie for me at work. Good thing too since I'm hungry. LOL
这篇关于自动筛选器在筛选视图中看不到数值数据(多个值)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
