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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
What is the error number & message?
 

slayer1957

New Member
Joined
Jan 9, 2017
Messages
28
When using * * it gives Run-time error '1004': AutoFilter method of Range class failed
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Do you get the same error with out the * ?
 

slayer1957

New Member
Joined
Jan 9, 2017
Messages
28

ADVERTISEMENT

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,

VBA 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

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

slayer1957

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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
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

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

Watch MrExcel Video

Forum statistics

Threads
1,126,928
Messages
5,621,635
Members
415,849
Latest member
PhoenixRising2015

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