Filter Returns Unfiltered Rows?
Posted by Joe Was on April 20, 2001 10:23 AM
Wild card search (Case 1) ignores criteria, "Tsearch" = False always & fails to be changed, "Temp" = "?X?????" should ="=?X?????"
Same code from Case 1 as stand alone, works on test sheet and fails in real application (only returns a copy of original unfiltered rows)? The code below works for Case 2 & 3 but returns a copy of all the data for Case 1. I am missing something fundamental?
Sub Search_Complex_Code()
Dim Temp, Tsearch
Dim Numsub
Temp = Application.InputBox(Prompt:="Please enter the Complexity Code to search for;" & Chr(13) & "Use a [ 7 digit search code ], only!" & Chr(13) & "You may use [ ? ] as a place holders, for digits which are not important!" & Chr(13) & "Example: ?G?????, [6 codes, 7 digits, ODA = 2 digits!]", Title:="Enter the Complexity Code to Lookup!", Type:=2)
If Temp = False Then
Numsub = 2
ElseIf Temp = "" Then
Numsub = 3
Else
Numsub = 1
End If
Select Case Numsub
Case 1
Tsearch = "" = " & Temp & """
With Worksheets("Parcel Fees")
.AutoFilterMode = False
.Rows(1).AutoFilter
.Rows(1).AutoFilter Field:=6, Criteria1:=Temp, VisibleDropDown:=False
.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("Search").Cells(1, 1)
Application.CutCopyMode = False
.AutoFilterMode = False
End With
Sheets("Search").Select
Range("A1").Select
Case 2
Sheets("Search").Select
Range("A1:A6").Select
Selection.EntireRow.Delete
Range("A1").Select
Sheets("Parcel Fees").Select
Range("A1").Select
Case 3
Mcan = MsgBox("Search Canceled, search criteria is Blank?", vbOKOnly, "BLANK SEARCH!")
Sheets("Search").Select
Range("A1:A6").Select
Selection.EntireRow.Delete
Range("A1").Select
Sheets("Parcel Fees").Select
Range("A1").Select
End Select
End Sub