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]

What is the error number & message?

 

[slayer1957]

When using * * it gives Run-time error '1004': AutoFilter method of Range class failed

 

[Fluff]

Do you get the same error with out the * ?

 

[slayer1957]

Yes, getting same error. Depends on which selection is made in the "cmbSearchColumn", but without * * it sometimes debug the following line as well with same error code,

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

Is the following code maybe correct, it checks for Unit, but how does it check the next line

If frmForm.cmbSearchColumn.Value = "Unit" Then
    
        shDatabase.Range("A1:P" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=sValue
    
    Else

 

[slayer1957]

It also does not copy cells to the SearchData sheet.

What does the first line do C:C >=2. Should that not change?
Maybe the code below will play out after the error

    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

 

[Fluff]

There's nothing obviously wrong with the code.
Do you have any merged cells?
Is the sheet protected?
Is the data in a structured table?

 

[slayer1957]

It is structured. It seems there is an table or something, i deleted some rows and then it works partially. Should the whole sheet be in a table format. seems when i changed the colors or and borders of the sheet it changed more than it should.

 

[Fluff]

You can either keep it as a structured table & change the code, or convert the table back to a range, in which case your code should work.
Your choice

 

[slayer1957]

I got this to work again, there was an issue on sheet and needed to redo the whole sheet.
It works when searching for text values but if there is numbers only it does not find anything, it only find numbers in the unit column but in the rest of the columns if there's a number it keeps on displaying no results.