Runtime error '1004' - Method 'Range' of object '_worksheet' falied

Hi All

I have a spreadsheet with only one sheet named "MasterDB" and has below columns. Here if BATCH: high then complaint number will be 1 and if another complaint with BATCH: high comes in then it will be given a complaint number as 2 and so on. similarily for complaints of type - Medium and Low

Image of the MasterDB is attached.

I have a userform with Two fields - Complaint Number and BATCH (dropdown with three values - High, Medium and Low). User will enter the complaint number and select the BATCH and clicks search button. My search button will search for the information in the MasterDB sheet and displays the result in a Listbox (Listbox1). Everything is working as i expect, but when user enters a complaint number which doesn't exist in the column 1 matching with the Complaint Type (Example: 2 and Low), then im getting below error message (Runtime error '1004' - Method 'Range' of object '_worksheet' falied) at highligted place

DataSH.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, criteriarange:=DataSH.Range("K1:L2"), copytorange:=DataSH.Range("N1:T1")
ListBox1.RowSource = "MasterDB!" & DataSH.Range("MatchResults").Address.

Hope i was clear with my question. Can anyone help me on how to handle the about error. I'd wish if there is no data then a message box should be displayed with the Message 'No data found, try again"

Regards
Vijay

Hi again,

I managed to get thru the error message. So whenever an error message is thrown at the below, Im displaying a message box.

On Error GoTo ErrorHandler
DataSH.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, criteriarange:=DataSH.Range("K1:L2"), copytorange:=DataSH.Range("N1:T1")
ResultList.RowSource = "MasterDB!" & DataSH.Range("MatchResults").Address


If Err.Number <> 0 Then
ErrorHandler: MsgBox "No matches found, Pls try again"

Though this is wrong, this is fulfilling my requirement. Awaiting for other answers..


Regards,
Vijay

do advanced filter in xlFilterInPlace and you can find

example

Please Login or Register  to view this content.