+ Reply to Thread
Results 1 to 12 of 12

How to gather multiple values from UserForm to locate any matches in spreadsheet

  1. #1
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 365
    Posts
    83

    Question How to gather multiple values from UserForm to locate any matches in spreadsheet

    I'm attempting to build a routine that will compare values from a UserForm with multiple columns of a spreadsheet and return all rows that match all of those values which were not blank.

    I'm not entirely certain how to go about it but I've started with a Search Subroutine and created a couple arrays
    Please Login or Register  to view this content.
    I've stored the values for any NON-Empty fields into both Fields() and Col()

    My hope was to cycle through each row in my spreadsheet, comparing the values stored in the Fields() array with the corresponding Col()array to indicate which column in the row to compare.
    Unfortunately some Fields are often unknown and as a result, have no value entered, so they are skipped as is the Col value.
    I'm having difficulty, visualizing how to handle those inevitable gaps.

    If only Fields(0), Fields(3) and Fields (5) have values stored and the same for Col(), how can I later, specifically call up those parts of the array for comparison to my spreadsheet.

    Any assistance would be welcomed and appreciated.

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,954

    Re: How to gather multiple values from UserForm to locate any matches in spreadsheet

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 365
    Posts
    83

    Re: How to gather multiple values from UserForm to locate any matches in spreadsheet

    I've been trying to add an attachment but once I've selected it on my computer, I'm asked to choose from Saving my own file or Opening my own file. I've tried both and neither uploads the file that I can see listed, to this website. Either a step in the instructions is missing or there's a glitch in the process.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 365
    Posts
    83

    Re: How to gather multiple values from UserForm to locate any matches in spreadsheet

    I guess the file transferred even though I had no indication at my end that it worked.

    I should explain that I got tired of typing in the search parameters over and over again so I used the Value property for my textboxes to save myself time when testing. Unfortunately now, when I Run the UserForm from the Debugger, the UserForm loads and unloads before I can click the Search button to activate my Sub cmdSearch_Click() where most of my code resides.

    I didn't understand about the BEFORE and After sheets but basically the current Search parameters Match a record in my datasheet and my goal is to have that and any other matches display in my SearchResults sheet.

    I'd be grateful for any guidance that you can share.

  5. #5
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,833

    Re: How to gather multiple values from UserForm to locate any matches in spreadsheet

    Give this alternative approach a go.
    The six upper comboboxes do the initial filter/search loading the listbox with a small number of matches.
    Selecting in the listbox displays all the relative fields on the form.
    torachan.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 365
    Posts
    83

    Re: How to gather multiple values from UserForm to locate any matches in spreadsheet

    That is VERY Elegant solution! Thank you so MUCH! This is far superior to what I hoped for. Two questions for you regarding this format...
    Is there a way to sort the listbox values in order to make them easier to find?
    Is it possible to add a field to the listbox display, that is NOT a search field? When a match is found and displayed, I'd like to include the value in Column B which identifies the branch of the family for that individual.
    THANK YOU SO VERY MUCH!!

  7. #7
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,954

    Re: How to gather multiple values from UserForm to locate any matches in spreadsheet

    Ok

    I have had to completely rebuild your database and your userform.

    I have attached "Instructions" for building Userforms that you might find useful.

    When you open the userform it now loads entry 1 from row 2 of the Database

    a. Delete the m in Adam and the listbox is cleared and loaded with Entry 1 from the database

    b. Delete the 2, the last number in date of birth 1842. the listbox is cleared and loaded with Entry 1 and entry 2 from the database.
    Both entries are identical but the date of births differ.

    Click on the Listbox to load the Entry.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 365
    Posts
    83

    Re: How to gather multiple values from UserForm to locate any matches in spreadsheet

    mehmetcik, Thank you so much for all your time, effort and especially for the helpful code examples. I must admit that most of it remains a bit "over my head" but I'm hopeful that if I study it a while longer, anything there that I can understand, will be an enormous help down the road.

    I noticed that you added a new column to the spreadsheet "Wife?". I thought I should explain that the only reason that I used a checkbox on my UserForm, to specifically identify wives, was so that when comparing the text entered in as a wife's first name to search for, it would be compared to the wife's first name WITHOUT the prefix of "m#: " that distinguishes first wives from second wives and etc.

  9. #9
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,833

    Re: How to gather multiple values from UserForm to locate any matches in spreadsheet

    The attached now has alpha-numeric sorted lists on comboboxes and listbox.
    The comboboxes are populated by cascading the sequence of data from the table, the table columns have to be contiguous.
    The extra textbox is populated from column "O", you can alter that if you wish without affecting the filtering.
    torachan.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 365
    Posts
    83

    Re: How to gather multiple values from UserForm to locate any matches in spreadsheet

    torachan, This is AMAZING and VERY helpful (even if the search RESULTS does not yet return the Family Branch that the individual descends from)! I will study your code and attempt to imitate how you directed the spreadsheet data to the extra textbox which is now populated by any notes and do the same for the family branch ID for the individual located. In the meantime, THANK YOU SO VERY MUCH for all the generous time, energy and wisdom that you've put into this project!

  11. #11
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,833

    Re: How to gather multiple values from UserForm to locate any matches in spreadsheet

    Look for the code below and change the "14" (Me.t15.Text = .List(sc, 14))to which column you require.
    Bearing in mind listbox references are zero based so sheet column 15 is listbox column 14.
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 365
    Posts
    83

    Re: How to gather multiple values from UserForm to locate any matches in spreadsheet

    Happy to report that it did NOT take me long to figure out how to add my Family Branch ID to information returned with matches. Thanks again for all your assistance!

+ 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. Userform: Save Multiple Rows that Matches Criteria
    By givemeaccessexcel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-30-2018, 12:55 AM
  2. Using Excel to Locate Matches from Multiple Lists
    By tcluer in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-28-2015, 12:42 PM
  3. [SOLVED] Excel Formula needed to locate multiple values in 1 spreadsheet
    By Cammyd in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-02-2014, 04:48 PM
  4. [SOLVED] Userform: How do I use text/values from a spreadsheet to customize the userform?
    By bananajelly in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-14-2014, 03:47 PM
  5. Using Values from Multiple Comboboxes to Locate information and display it.
    By Jayruss in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-11-2012, 06:48 AM
  6. Using for loop to gather multiple values from different sheets
    By alexwgordon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-16-2011, 08:08 PM
  7. Using multiple values to locate exact values
    By jgg in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-11-2009, 06:58 AM

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