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]

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

That indicates the same problem as earlier. I don't think that the value held by the variable sColumn can be found (exactly) in the range A1:M1 on Sheet 'Database'

You seemed to be saying that sColumn holds the value "Asset No."
Is that correct?

If so, what cell in A1:M1 of 'Database' do you think also holds that exact value?

 

[sarrhian]

Sorry, I'm having a bit of trouble understanding exactly what you mean.. I have attached my worksheet with dummy data. I really appreciate your help here!

dummy sheet.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3		Details of Asset						Owner		Details of Inspection
4		Asset No.	Description of Asset (Inlucding model number)	Serial Number	Supplier	Manufacturer	Purchase Date	L5Y/XYZ		Interval	Period Inspected	Located Yes/No	Condition
5	1	BRE-A001	Computer Desk	123ABCDEFG	IKEA	IKEA	15/02/2021	XYZ		Yearly	Q3-2020	Yes	Good
6	2	BRE-A002	Computer Screen	GFEDCBA321	IKEA	IKEA	15/02/2021	L5Y		Yearly	Q3-2020	Yes	Good
7	3	BRE-A003	Printer	HIJKLMN123	Austin	Brother	23/03/2019	L5Y		Yearly	Q3-2020	Yes	Good
8	4	BRE-A004	Desktop Computer	321NMLKJIH	Austin	Dell	20/05/2019	XYZ		Quarterly	Q2-2020	No	N/A
9	5	BRE-A005	Desktop Computer	QWERTY678	Austin	Dell	19/03/2020	XYZ		Quarterly	Q4-2020	Yes	Good
10	6	BRE-A006	Laptop 1	LAPT123	Austin	Asus	15/06/2021	XYZ		Quarterly	Q2-2020	Yes	Good
11	7	BRE-A007	Laptop 2	LAPT234	Austin	Asus	30/04/2020	XYZ		Quarterly	Q2-2020	Yes	Good
12	8	BRE-A008	3D Printer	PRiN323	Austin	N/A	1/01/2022	L5Y		Quarterly	Q3-2020	Yes	Good
Database

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D5:D63	Cell Value	duplicates	text	YES

 

[Alex Blakenburg]

iColumn = Application.Match(sColumn, shDatabase.Range("A1:M1"), 0)
Why are you looking in Row 1 when Asset No. appears in row 4 ?

 

[sarrhian]

iColumn = Application.Match(sColumn, shDatabase.Range("A1:M1"), 0)
Why are you looking in Row 1 when Asset No. appears in row 4 ?

One of those moments of not noticing the little things I guess... x.x
The same error still shows up even when changed to A4:M4 though.

 

[Alex Blakenburg]

The XL2BB you sent is definitely of the sheet "Database" right ?

Just for testing can you replace the line iColumn line with the below:-
and let us know what you get in the immediate window.

PS: You will also need to change yor other references to shDatabase.Range("A1 to be to A4

    sColumn = "Asset No."               ' Add this line for testing
    iColumn = Application.Match(sColumn, shDatabase.Range("A4:M4"), 0)  ' Changed to row 4
    Debug.Print iColumn                 ' Add this line for testing

 

[Peter_SSs]

I would be interested to see you insert this blue line of code where shown then run the code and see what the message box produces.

iDatabase = ThisWorkbook.Sheets("Database").Range("A" & Application.Rows.Count).End(xlUp).Row
sColumn = frmData.cmbSearch.Value
MsgBox sColumn = shDatabase.Range("B4").Value

 

[sarrhian]

I would be interested to see you insert this blue line of code where shown then run the code and see what the message box produces.

iDatabase = ThisWorkbook.Sheets("Database").Range("A" & Application.Rows.Count).End(xlUp).Row
sColumn = frmData.cmbSearch.Value
MsgBox sColumn = shDatabase.Range("B4").Value

Sorry for the late reply, life things came up. The message box comes up with False.

 

[sarrhian]

The XL2BB you sent is definitely of the sheet "Database" right ?

Just for testing can you replace the line iColumn line with the below:-
and let us know what you get in the immediate window.

PS: You will also need to change yor other references to shDatabase.Range("A1 to be to A4

    sColumn = "Asset No."               ' Add this line for testing
    iColumn = Application.Match(sColumn, shDatabase.Range("A4:M4"), 0)  ' Changed to row 4
    Debug.Print iColumn                 ' Add this line for testing

Definitely Database.
When I use this code, it comes up with a message box saying True, then removed all of the data from the worksheet once it ran through the debug line.
Editing to clarify, I ran the code to search, it came up with the "True" message box and removed all of the data, so I entered some dummy data again, then when I stepped through the code in VBA it removed it all at the debug line.

 

[Alex Blakenburg]

Scrap that let me. Let me edit this first

 

[Alex Blakenburg]

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