Search Data Debugging problem

[slayer1957]

Good day,

I have the following code on module for userform, Basically it is a drop down to select the filter column and then you can type in the search box field either exact number/word or use *value* and it should search the range and display filtered results.

It keeps on giving error to debug the following line.

shDatabase.Range("A1:P" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=sValue

or

shDatabase.Range("A1:P" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:="*" & sValue & "*"

The headings on the Database sheet is All, Unit, Month of Failure, Notification, Order, Order Type, Technical Id, Main Work Centre, Description, Reported By, Submitted On.

Headings in Columns A1-P1

Can someone please assist, what might be wrong?

Sub Add_SearchColumn()

    frmForm.EnableEvents = False

    With frmForm.cmbSearchColumn
    
        .Clear
        
        .AddItem "All"
        
        .AddItem "Unit"
        .AddItem "Month of Failure"
        .AddItem "Notification"
        .AddItem "Order"
        .AddItem "Order Type"
        .AddItem "Technical ID"
        .AddItem "Main Work Centre"
        .AddItem "Description"
        .AddItem "Reported By"
        .AddItem "Submitted On"
        
        .Value = "All"
    
    
    End With
    
    frmForm.EnableEvents = True
    
    frmForm.txtSearch.Value = ""
    frmForm.txtSearch.Enabled = False
    frmForm.cmdSearch.Enabled = False

End Sub

Sub SearchData()

    Application.ScreenUpdating = False
    
    Dim shDatabase As Worksheet ' Database sheet
    Dim shSearchData As Worksheet 'SearchData sheet
    
    Dim iColumn As Integer 'To hold the selected column number in Database sheet
    Dim iDatabaseRow As Long 'To store the last non-blank row number available in Database sheet
    Dim iSearchRow As Long 'To hold the last non-blank row number available in SearachData sheet
    
    Dim sColumn As String 'To store the column selection
    Dim sValue As String 'To hold the search text value
    
    
    Set shDatabase = ThisWorkbook.Sheets("Database")
    Set shSearchData = ThisWorkbook.Sheets("SearchData")
    
    
    iDatabaseRow = ThisWorkbook.Sheets("Database").Range("A" & Application.Rows.Count).End(xlUp).Row
    
    
    sColumn = frmForm.cmbSearchColumn.Value
    
    sValue = frmForm.txtSearch.Value
    
    
    iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A1:P1"), 0)
    
    'Remove filter from Database worksheet
    
    If shDatabase.FilterMode = True Then
    
        shDatabase.AutoFilterMode = False
    
    End If

    'Apply filter on Database worksheet
    
    If frmForm.cmbSearchColumn.Value = "Unit" Then
    
        shDatabase.Range("A1:P" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=sValue
    
    Else
    
        shDatabase.Range("A1:P" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:="*" & sValue & "*"
    
    End If
    
    If Application.WorksheetFunction.Subtotal(3, shDatabase.Range("C:C")) >= 2 Then
    
        'Code to remove the previous data from SearchData worksheet
        
        shSearchData.Cells.Clear
        
        shDatabase.AutoFilter.Range.Copy shSearchData.Range("A1")
        
        Application.CutCopyMode = False
        
        iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
        
        frmForm.lstDatabase.ColumnCount = 16
        
        frmForm.lstDatabase.ColumnWidths = "30, 60, 75, 40, 60, 45, 55, 70, 70, 30, 30, 30, 30, 30, 30, 30"
        
        If iSearchRow > 1 Then
        
            frmForm.lstDatabase.RowSource = "SearchData!A2:P" & iSearchRow
            
            MsgBox "Records found."
        
        End If
        
        
    Else
    
       MsgBox "No record found."
    
    End If

    shDatabase.AutoFilterMode = False
    Application.ScreenUpdating = True


End Sub

 

[Fluff]

As far as I know you cannot do a wildcard match with numbers.