AdvFilter will not return search results

R

Rfriend

Board Regular
Joined
May 10, 2021
Messages
73
Office Version
  1. 2010
Platform
  1. Windows
For some reason my advanced filted does not return any results. When All_Columns is used the correct column header is returned to the text box, but nothinhg is returned to the list box and I get an error of "MsgBox "No match found for " & txtSearch.Text ' error point." All_Columns will return the header where the information lives but no information.

Any thoughts would be appreciated.

Column Headers used for search criteria are:
  • Item
  • Department
  • Year
  • Type/Model
  • Serial
  • All_Columns
I have recorded the macro several times. At one point it worked then I got the error. I rercorded the macro and error.
VBA Code: 
Private Sub cmdLookup_Click()
    Dim Crit As Range
    Dim FindMe As Range
    Dim MobileSH As Worksheet
        On Error GoTo errHandler:

     Set MobileSH = Sheet24
        Application.ScreenUpdating = False
    If Me.cboHeader.Value <> "All_Columns" Then
    If Me.txtSearch.Value = "" Then
            MobileSH.Range("S7") = ""
    Else
            MobileSH.Range("S7") = "*" & Me.txtSearch.Value & "*"
        End If
    End If
    If Me.cboHeader.Value = "All_Columns" Then
        Set FindMe = MobileSH.Range("B9:I100000").Find(What:=txtSearch.Value, LookIn:=xlValues, _
            lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        Set Crit = MobileSH.Cells(8, FindMe.Column)
    If Me.txtSearch.Value = "" Then
        MobileSH.Range("S7") = ""
        MobileSH.Range("S6") = ""
    Else
        MobileSH.Range("S6") = Crit
    If Crit = "ID" Then
        MobileSH.Range("S7") = Me.txtSearch.Value
    Else
        MobileSH.Range("S7") = "*" & Me.txtSearch.Value & "*"
    End If
        Me.txtAllColumn = MobileSH.Range("S6").Value
    End If
    End If
    Unprotect_All
        Sheet24.Range("H9:I100000").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheet24.Range("S6:S7"), CopyToRange:=Sheet24.Range("W8:AI8"), _
        Unique:=False  '[B]AdvFilterMacro then straight to error handler MsgBox[/B]
           lstLookup.RowSource = MobileSH.Range("MobileEquip").Address(external:=True)
        Protect_All
    Exit Sub
errHandler:
   Protect_All
        MsgBox "No match found for " & txtSearch.Text  ' [B]error point[/B]
        On Error GoTo 0
    Exit Sub
End Sub
 

Attachments

  • Capture.GIF
    Capture.GIF
    100.9 KB · Views: 8

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Hello @Rfriend , thanks for posting on the forum.

I didn't understand some conditions in your code.

But review the following for you to see if it makes sense to you.
- You select the header and write a text, then the filter does it based on that header and text.
- If you select "All_Columns", then it searches the entire range, if found it takes the header of the data found and performs the filter.​

VBA Code: 
Private Sub cmdLookup_Click()
  Dim FindMe As Range, s6 As Range, s7 As Range
  Dim sh As Worksheet
  Dim lr As Long
  
  Set sh = Sheet24
  Set s6 = sh.Range("S6")   'header
  Set s7 = sh.Range("S7")   'item
  
  lr = sh.Range("B:I").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  s6.Value = ""
  s7.Value = ""
  
  If cboHeader.Value <> "All_Columns" Then
    s6.Value = cboHeader.Value
  Else
    Set FindMe = sh.Range("B9:I" & lr).Find(txtSearch.Value, , xlValues, xlPart, , , False)
    If Not FindMe Is Nothing Then s6.Value = sh.Cells(8, FindMe.Column)
  End If
  
  If txtSearch.Value <> "" Then s7.Value = "*" & txtSearch.Value & "*"
  Unprotect_All
  sh.Range("B8:I" & lr).AdvancedFilter xlFilterCopy, sh.Range("S6:S7"), sh.Range("W8:AD8"), False
  lstLookup.RowSource = sh.Range("W8:AD" & sh.Range("W" & Rows.Count).End(3).Row).Address(external:=True)
  Protect_All
End Sub
Test the code and review the behavior. 🙂
Maybe the code needs to fine tune some details, but I need some examples to test, what do you want to filter and what do you want in the output. 😉

To give examples use the XL2BB tool.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
 
Upvote 0
DanteAmor said:
Hello @Rfriend , thanks for posting on the forum.

I didn't understand some conditions in your code.

But review the following for you to see if it makes sense to you.
- You select the header and write a text, then the filter does it based on that header and text.​
- If you select "All_Columns", then it searches the entire range, if found it takes the header of the data found and performs the filter.​

VBA Code: 
Private Sub cmdLookup_Click()
  Dim FindMe As Range, s6 As Range, s7 As Range
  Dim sh As Worksheet
  Dim lr As Long
 
  Set sh = Sheet24
  Set s6 = sh.Range("S6")   'header
  Set s7 = sh.Range("S7")   'item
 
  lr = sh.Range("B:I").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  s6.Value = ""
  s7.Value = ""
 
  If cboHeader.Value <> "All_Columns" Then
    s6.Value = cboHeader.Value
  Else
    Set FindMe = sh.Range("B9:I" & lr).Find(txtSearch.Value, , xlValues, xlPart, , , False)
    If Not FindMe Is Nothing Then s6.Value = sh.Cells(8, FindMe.Column)
  End If
 
  If txtSearch.Value <> "" Then s7.Value = "*" & txtSearch.Value & "*"
  Unprotect_All
  sh.Range("B8:I" & lr).AdvancedFilter xlFilterCopy, sh.Range("S6:S7"), sh.Range("W8:AD8"), False
  lstLookup.RowSource = sh.Range("W8:AD" & sh.Range("W" & Rows.Count).End(3).Row).Address(external:=True)
  Protect_All
End Sub
Test the code and review the behavior. 🙂
Maybe the code needs to fine tune some details, but I need some examples to test, what do you want to filter and what do you want in the output. 😉

To give examples use the XL2BB tool.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
Click to expand...
I reviewed and it I get everything except the "sh.Range("W8:AD" & sh.Range("W" & Rows.Count).End(3).Row)" code. Not sure what that means. I see it replaced the named range of "MobileEquip" however, I am not sure what is says.

I placed the code into the workbook and it works except the headers fall outside the header row in the listbox, The headers are row 8 but the listbox is showing the data from row 7 in the headers. The item count is correct when searching and using "All_Columns".

Thank you.
 

Attachments

  • Capture.GIF
    Capture.GIF
    197.2 KB · Views: 8
Upvote 0
Rfriend said:
I see it replaced the named range of "MobileEquip" however, I am not sure what is says.
Click to expand...

Check how you got your range "MobileEquip" or put my code: sh.Range("W8:AD" & sh.Range("W" & Rows.Count).End(3).Row)
It means that you are going to put the data from cell W8 to column AD and to the last row with data. It has the advantage that it does not load blank rows in the listbox and your range can have blank rows :cool:

If the problem continues, I would have to look at your file to see how all the components are doing.
You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
 
Upvote 0
DanteAmor said:
Hello @Rfriend , thanks for posting on the forum.

I didn't understand some conditions in your code.

But review the following for you to see if it makes sense to you.
- You select the header and write a text, then the filter does it based on that header and text.​
- If you select "All_Columns", then it searches the entire range, if found it takes the header of the data found and performs the filter.​

VBA Code: 
Private Sub cmdLookup_Click()
  Dim FindMe As Range, s6 As Range, s7 As Range
  Dim sh As Worksheet
  Dim lr As Long
 
  Set sh = Sheet24
  Set s6 = sh.Range("S6")   'header
  Set s7 = sh.Range("S7")   'item
 
  lr = sh.Range("B:I").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  s6.Value = ""
  s7.Value = ""
 
  If cboHeader.Value <> "All_Columns" Then
    s6.Value = cboHeader.Value
  Else
    Set FindMe = sh.Range("B9:I" & lr).Find(txtSearch.Value, , xlValues, xlPart, , , False)
    If Not FindMe Is Nothing Then s6.Value = sh.Cells(8, FindMe.Column)
  End If
 
  If txtSearch.Value <> "" Then s7.Value = "*" & txtSearch.Value & "*"
  Unprotect_All
  sh.Range("B8:I" & lr).AdvancedFilter xlFilterCopy, sh.Range("S6:S7"), sh.Range("W8:AD8"), False
  lstLookup.RowSource = sh.Range("W8:AD" & sh.Range("W" & Rows.Count).End(3).Row).Address(external:=True)
  Protect_All
End Sub
Test the code and review the behavior. 🙂
Maybe the code needs to fine tune some details, but I need some examples to test, what do you want to filter and what do you want in the output. 😉

To give examples use the XL2BB tool.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
Click to expand...
The only other thing is when you leave the search criteria blank on the userform it should return all 205 items, instead it only returns the headers as row i.
 

Attachments

  • Capture.GIF
    Capture.GIF
    218.2 KB · Views: 7
Upvote 0
DanteAmor said:
Maybe the code needs to fine tune some details, but I need some examples to test, what do you want to filter and what do you want in the output. 😉
Click to expand...
I don't understand what you mean and I don't understand your image either.
As I told you I don't have data to test and I also commented that maybe some adjustments are necessary since I don't understand what you need to filter.

The code, I think, is clearer and I think that from that point you can optimize what you need at the end.

If you want help with a filter or something that is not working for you, then you must provide the data from the sheet, what data are you putting in the userform and what you expect as a result, all examples using the XL2BB tool.

What you put in the output range is what will be loaded in the listbox, but the load in the listbox is the least of it, what you must explain is the above.

If it's hard to explain what you need, then share your file and explain which scenario you need help with.

Without that information it is difficult for me to help you.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
 
Upvote 0
DanteAmor said:
Check how you got your range "MobileEquip" or put my code: sh.Range("W8:AD" & sh.Range("W" & Rows.Count).End(3).Row)
It means that you are going to put the data from cell W8 to column AD and to the last row with data. It has the advantage that it does not load blank rows in the listbox and your range can have blank rows :cool:

If the problem continues, I would have to look at your file to see how all the components are doing.
You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
Click to expand...
Here is a link to a redacted version.

Employee Training Inventory Database
 
Upvote 0
DanteAmor said:
I don't understand what you mean and I don't understand your image either.
As I told you I don't have data to test and I also commented that maybe some adjustments are necessary since I don't understand what you need to filter.

The code, I think, is clearer and I think that from that point you can optimize what you need at the end.

If you want help with a filter or something that is not working for you, then you must provide the data from the sheet, what data are you putting in the userform and what you expect as a result, all examples using the XL2BB tool.

What you put in the output range is what will be loaded in the listbox, but the load in the listbox is the least of it, what you must explain is the above.

If it's hard to explain what you need, then share your file and explain which scenario you need help with.

Without that information it is difficult for me to help you.

--------------
I hope to hear from you soon.
Respectfully
--------------
Click to expand...


I added a link to a copy of the workbook. I put a button on each page so you can unprotect. The workbook protects its self with every action. On the interface the "Mobile Equipment" button is the user form. The code is supposed to go to the tab "Mobile" and search the left side data based on the criteria in S6 & S7., then return the output in W9. The filters are the selections of the combo box and the text box, or the all_columns which returns the location of the first occurance of the text box filter i.e. 2023 would produce "Year". The code you provided places the headers below the list box header row, and leaving the search criteria blank produces no results. That should return all the data unfiltered. Not sure I am answering your question or not, let me know, and thanks for the help.

Employee Training Inventory Database
 
Upvote 0
DanteAmor said:
I don't understand what you mean and I don't understand your image either.
As I told you I don't have data to test and I also commented that maybe some adjustments are necessary since I don't understand what you need to filter.

The code, I think, is clearer and I think that from that point you can optimize what you need at the end.

If you want help with a filter or something that is not working for you, then you must provide the data from the sheet, what data are you putting in the userform and what you expect as a result, all examples using the XL2BB tool.

What you put in the output range is what will be loaded in the listbox, but the load in the listbox is the least of it, what you must explain is the above.

If it's hard to explain what you need, then share your file and explain which scenario you need help with.

Without that information it is difficult for me to help you.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
Click to expand...
The image (top) shows what was returned with the search criteria in the image, andd shows the header row as row 1 in the listbox (below the header row).

The second image is the location of the data and the criteria.

The third shows the results of the advanced filter when the combo and text boxes are left blank. That should return all items in the database (sheet24, Mobile), but this returns just the header row as row 1 of the listbox.
 
Upvote 0
DanteAmor said:
I don't understand what you mean and I don't understand your image either.
As I told you I don't have data to test and I also commented that maybe some adjustments are necessary since I don't understand what you need to filter.

The code, I think, is clearer and I think that from that point you can optimize what you need at the end.

If you want help with a filter or something that is not working for you, then you must provide the data from the sheet, what data are you putting in the userform and what you expect as a result, all examples using the XL2BB tool.

What you put in the output range is what will be loaded in the listbox, but the load in the listbox is the least of it, what you must explain is the above.

If it's hard to explain what you need, then share your file and explain which scenario you need help with.

Without that information it is difficult for me to help you.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
Click to expand...
Any suggestions?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Method range of object _worksheet failed 1004 vba
Replies
4
Views
718
Rfriend
R
R
  • Question
AdvFilter will return data to named range but not show in userform list box.
Replies
0
Views
398
Rfriend
R
R
  • Question
Search for values using an advanced search and update then display pivot table results in a userform listbox
Replies
1
Views
436
Rfriend
R
S
  • Question
VBA Code - Search numbers and text
Replies
5
Views
6K
DanteAmor
DanteAmor
D
  • Question
Multipage userform with listbox not receiving data from vba filter
Replies
0
Views
1K
Dungadin
D

Forum statistics

Threads
1,215,732
Messages
6,126,540
Members
449,316
Latest member
sravya

Share this page

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
Back
Top