AutoFilter method of Range class failed, Error 1004

[sarrhian]

Hi there, I consider myself a novice at VBA and have managed to put together a UserForm that can search a worksheet and edit data. At some point along the line I seem to have broken the code as I can no longer search. The below is my code. The piece of code giving me this error is in red.
Code was written in Excel 2016 and also does not work in Excel 365.

Sub SearchData()

    Application.ScreenUpdating = False
    Dim shDatabase As Worksheet 'Database Sheet
    Dim shSearchData As Worksheet 'Searchdata Sheet
    Dim iColumn As Variant
    Dim iDatabase As Long
    Dim iSearchRow As Long
    Dim sColumn As String
    Dim sValue As String
   
    Set shDatabase = ThisWorkbook.Sheets("Database")
    Set shSearchData = ThisWorkbook.Sheets("SearchData")
   
 
    iDatabase = ThisWorkbook.Sheets("Database").Range("A" & Application.Rows.Count).End(xlUp).Row
    sColumn = frmData.cmbSearch.Value
   
    sValue = frmData.txtSearch.Value
   
   
    iColumn = Application.Match(sColumn, shDatabase.Range("A1:M1"), 0)
   
    If shDatabase.FilterMode = True Then
        shDatabase.AutoFilterMode = False
    End If
   
    If frmData.cmbSearch.Value = "Asset No." Then
        shDatabase.Range("A1:M" & iDatabase).AutoFilter Field:=iColumn, Criteria1:=sValue
    Else
        shDatabase.Range("A1:M" & iDatabase).AutoFilter Field:=iColumn, Criteria1:="*" & sValue & "*"
    End If
   
    If Application.WorksheetFunction.Subtotal(3, shDatabase.Range("C:C")) >= 2 Then
   
        'Code to remove previous search data
        shSearchData.Cells.Clear
       
        shDatabase.AutoFilter.Range.Copy shSearchData.Range("A1")
       
        Application.CutCopyMode = False
        iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
       
        frmData.lstDatabase.ColumnCount = 12
        frmData.lstDatabase.ColumnWidths = "0,60,75,40,60,45,55,0,70,70,70,70"
       
        If iSearchRow > 1 Then
            frmData.lstDatabase.RowSource = "SearchData!A2:T" & iSearchRow
        End If
    Else
        MsgBox "No record found."
    End If
   
    shDatabase.AutoFilterMode = False
    Application.ScreenUpdating = True
   
   
End Sub

Any help would be greatly appreciated!

 

[Peter_SSs]

there seems to be a mismatch on what is being typed into the TextBox and the heading.

Basically what I have been trying to get at since post #5.

The two conversations in this thread seem to be getting muddled & hard to follow. Therefore I am withdrawing for now & will leave it to Alex to try to resolve. I will continue to watch in case there is something useful that I can add.

 

[sarrhian]

Based on the testing there seems to be a mismatch on what is being typed into the TextBox and the heading.
The most likely issue is that the full stop is not being typed into the text box.

It might be worth considering replacing your match line iColumn = Application.Match(sColumn, shDatabase.Range("A4:M4"), 0) with something line this:

    Dim rngFound As Range
    With shDatabase.Range("A4:M4")
        Set rngFound = .Find(What:=sColumn, LookIn:=xlValues, MatchCase:=False, LookAt:=xlPart)
        If Not rngFound Is Nothing Then
            iColumn = rngFound.Column
        Else
            MsgBox "sColumn value: " & sColumn & " not found - exiting routine"
            Exit Sub
        End If
    End With

That did it! Thank you so much for the help, you're a genius!! I definitely need to brush up on my skills more and probably post these sorts of things a bit earlier in the work day when I'm not so tired haha. I really appreciate the patience you two have shown!

 

[Alex Blakenburg]

Glad we could help & thanks for the feedback.