Search Data Debugging problem

slayer1957

New Member
Joined
Jan 9, 2017
Messages
28
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.

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

Code:
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?


Code:
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



VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,252
Office Version
  1. 365
Platform
  1. Windows
As far as I know you cannot do a wildcard match with numbers.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Watch MrExcel Video

Forum statistics

Threads
1,126,984
Messages
5,621,981
Members
415,871
Latest member
Frank_Aa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top