+ Reply to Thread
Results 1 to 11 of 11

Excel VBA Userform database search - Advanced Filter

  1. #1
    Registered User
    Join Date
    01-17-2017
    Location
    London
    MS-Off Ver
    2016
    Posts
    7

    Excel VBA Userform database search - Advanced Filter

    Hello..

    I am having few issues with regards to the results of the Advanced search VBA. Hope you can assist me with this problem.

    This is a simple userform with a text box and a list box. The aim is not to match all the case, but to search the data for any match and then display the rows of the matches. For example if I search for "Ex", the result would display all rows of the data that have somewhere in the row words similar to "Ex" such as "Excel", "Example" .... Please see attached excel to help understand better.

    THE PROCESS:

    1. The user types in the text box what to search for,
    2. The value of the textbox is sent to "L9" cell in the workbook
    3. An advanced filter runs that searches the available data for the value of cell "L9" and then pastes the filtered results in the allocated range.
    4. The listbox mirrors the results in the allocated range.

    All seem to work well and perfect. Except when the search is done, if a match is found in a specific column "E", it does not carry on to search all the remaining columns and only gives the results found in that column.

    Example: in the attached excel: We have the word "TEST" in four cells: B14, D12, D13, F15 & H17 - When we search for the word TEST and run it, it only gives the following results: D12 & D13 (That is Row 12 & Row 13) - it does not look in other columns.


    Please advice on this issue.


    Thank you

    Best Regards
    Amir
    Attached Files Attached Files
    Last edited by AmirHub; 01-24-2017 at 09:38 AM.

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,379

    Re: Excel VBA Userform database search - Advanced Filter

    No work on the sheet, all in memory

    Please Login or Register  to view this content.
    Kind regards
    Leo
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-17-2017
    Location
    London
    MS-Off Ver
    2016
    Posts
    7

    Re: Excel VBA Userform database search - Advanced Filter

    Hello Leo, Thanks a lot for your reply. The solution seems neat and simple but I couldn't get it to work for some reason. It doesn't give results for searched text.

    Thanks

    Best Regards
    Amir

  4. #4
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,379

    Re: Excel VBA Userform database search - Advanced Filter

    Amir,

    doesn't give result ? or not all ?

    Not all could be because it is Case sensitive, so Example is not the same as example or EXEMPLE


    Kind regards
    Leo
    Last edited by LeoTaxi; 01-30-2017 at 08:17 AM.

  5. #5
    Registered User
    Join Date
    01-17-2017
    Location
    London
    MS-Off Ver
    2016
    Posts
    7

    Re: Excel VBA Userform database search - Advanced Filter

    Leo, It turns out it is case sensitive and if written correctly it produces results.

    However, if I search for the term "FIND", it locates all but the one in cell "E17". But when I search the text "TEST" it gives correctly all the results (Please see attached pic).

    This is similar to the problem I first had.

    Thanks!
    Attached Images Attached Images

  6. #6
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,379

    Re: Excel VBA Userform database search - Advanced Filter

    Shame on me, forget 1 column

    Change
    Please Login or Register  to view this content.
    To
    Please Login or Register  to view this content.
    Kind regards
    Leo
    Last edited by LeoTaxi; 01-30-2017 at 08:11 AM.

  7. #7
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,379

    Re: Excel VBA Userform database search - Advanced Filter

    And the new file


    Cheers
    Leo
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-17-2017
    Location
    London
    MS-Off Ver
    2016
    Posts
    7

    Re: Excel VBA Userform database search - Advanced Filter

    Works perfectly.. I will now use your method and try and tweak it for case sensitivity and auto detection of rows and add it to my project. Hope all goes well.

    Thanks a lot!

    Cheers
    Amir

  9. #9
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: Excel VBA Userform database search - Advanced Filter

    Hi,
    To avoid case sensitivity you could use LCase().
    Just make all characters as LowerCase when you search, like this:
    Please Login or Register  to view this content.
    This will match Test and tEsT as equal.

  10. #10
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,379

    Re: Excel VBA Userform database search - Advanced Filter

    Amir,

    for Case sensitive kasan give solution, for this auto row detection, explain ?


    cheers
    Leo

  11. #11
    Registered User
    Join Date
    01-17-2017
    Location
    London
    MS-Off Ver
    2016
    Posts
    7

    Re: Excel VBA Userform database search - Advanced Filter

    @ kasan.. Thanks it Worked!

    @ Leo.. Because I was aiming to add lots of data in the worksheet, I did't want to have to enter a sentence for each row. It's working perfect now. I used the last row detect code to get the number of rows. Thanks for all your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Userform to search Excel used as database
    By Taoyuan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-02-2016, 06:50 PM
  2. Userform, Search, copy an paste data from a userform database to another sheet.
    By masro in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2015, 02:42 PM
  3. Work with excel 2007: Filter for search, designed database
    By thienlyhd in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-27-2013, 05:50 AM
  4. Search Through Database And Display Results In Userform without showing database
    By nirvanarapeme in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-04-2012, 05:18 PM
  5. Filter large database using Advanced Filter
    By thegrimmster in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-10-2011, 11:06 AM
  6. userform to search excel database
    By ncaravela in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-06-2010, 07:38 AM
  7. Using advanced filter to create new sheets based on database
    By eighty2scrambler in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-11-2009, 03:58 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1