For some reason my advanced filted does not return any results. When All_Columns is used the correct column header is returned to the text box, but nothinhg is returned to the list box and I get an error of "MsgBox "No match found for " & txtSearch.Text ' error point." All_Columns will return the header where the information lives but no information.
Any thoughts would be appreciated.
Column Headers used for search criteria are:
Any thoughts would be appreciated.
Column Headers used for search criteria are:
- Item
- Department
- Year
- Type/Model
- Serial
- All_Columns
VBA Code:
Private Sub cmdLookup_Click()
Dim Crit As Range
Dim FindMe As Range
Dim MobileSH As Worksheet
On Error GoTo errHandler:
Set MobileSH = Sheet24
Application.ScreenUpdating = False
If Me.cboHeader.Value <> "All_Columns" Then
If Me.txtSearch.Value = "" Then
MobileSH.Range("S7") = ""
Else
MobileSH.Range("S7") = "*" & Me.txtSearch.Value & "*"
End If
End If
If Me.cboHeader.Value = "All_Columns" Then
Set FindMe = MobileSH.Range("B9:I100000").Find(What:=txtSearch.Value, LookIn:=xlValues, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Set Crit = MobileSH.Cells(8, FindMe.Column)
If Me.txtSearch.Value = "" Then
MobileSH.Range("S7") = ""
MobileSH.Range("S6") = ""
Else
MobileSH.Range("S6") = Crit
If Crit = "ID" Then
MobileSH.Range("S7") = Me.txtSearch.Value
Else
MobileSH.Range("S7") = "*" & Me.txtSearch.Value & "*"
End If
Me.txtAllColumn = MobileSH.Range("S6").Value
End If
End If
Unprotect_All
Sheet24.Range("H9:I100000").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet24.Range("S6:S7"), CopyToRange:=Sheet24.Range("W8:AI8"), _
Unique:=False '[B]AdvFilterMacro then straight to error handler MsgBox[/B]
lstLookup.RowSource = MobileSH.Range("MobileEquip").Address(external:=True)
Protect_All
Exit Sub
errHandler:
Protect_All
MsgBox "No match found for " & txtSearch.Text ' [B]error point[/B]
On Error GoTo 0
Exit Sub
End Sub