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!

 

[sarrhian]

My apologies, the code that is giving the error is this line

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

 

[Peter_SSs]

Welcome to the MrExcel board!

My apologies, the code that is giving the error is this line

If you want to format code yourself like that you have to use the "rich" code tags. My signature block below has more details. I fixed the tags for you this time.

When you get the error and debug, in that error line hover over iDatabase, then iColumn then sValue and report the values shown in the pop-ups.

 

[sarrhian]

Welcome to the MrExcel board!


If you want to format code yourself like that you have to use the "rich" code tags. My signature block below has more details. I fixed the tags for you this time.

When you get the error and debug, in that error line hover over iDatabase, then iColumn then sValue and report the values shown in the pop-ups.

Thanks a heap! I'll definitely remember that!

iDatabase=63
iColumn=Error2042
sValue=BRE-A007 (This one is the item I am trying to search for.)

Clearly an issue with iColumn I guess, but I have no idea how to fix it.

 

[Peter_SSs]

Clearly an issue with iColumn I guess, but I have no idea how to fix it.

Yes, it seems this line is not finding a match

iColumn = Application.Match(sColumn, shDatabase.Range("A1:M1"), 0)

Run the code again and at the error hover over the sColumn variable in the above line and see if anything obvious turns up.

 

[sarrhian]

Yes, it seems this line is not finding a match

iColumn = Application.Match(sColumn, shDatabase.Range("A1:M1"), 0)

Run the code again and at the error hover over the sColumn variable in the above line and see if anything obvious turns up.

It still comes up as Asset No. Which makes sense as that is what is entered into the combobox on the form. It also changes correctly, into other options in the combobox.

 

[Peter_SSs]

It still comes up as Asset No. Which makes sense as that is what is entered into the combobox on the form.

Then if there is an error with "Asset No." then check both the Combo Box value and the value in A1:M1 that there are not leading or trailing space characters with one or both of them or even multiple spaces between "Asset" and "No."

 

[sarrhian]

I can't see any issues of the sort. It seems to be an error with iColumn in it's entirety as, no matter what I select in the combobox, it still returns the same error and the same Error2042.

 

[Peter_SSs]

Try

Dim iColumn As Long

 

[sarrhian]

Try

Dim iColumn As Long

I then run into the issue of a Type Mismatch, error 13 on the below code.

iColumn = Application.Match(sColumn, shDatabase.Range("A1:M1"), 0)