Pulling multiple Values from a list

  • Hey guys!


    I have already done a search to no avail, but if someone has seen this in another post, please let me know what to search for so I don't waste anyones time.


    For a simple example, I have a range with a list of Sales People, and another range with the list of accounts. Here is an example:
    Range1 Range2 Range3
    Bob Smith Bubba's Shoes NorthEast
    Bob Smith Xanadu's SouthWest
    Gary Bob Ripunzels Eastern
    Fay Bon Frickafrack Northern
    Bob Smith Franks & Beans Central


    Now what I would like to do is have a combo box with each salesman, then be able to pick a salesman like Bob Smith and have it populate cells with all of his acounts.
    I do not need the whole program, just the direction on how to go about pulling and listing from one criteria. I can't use a pivot table, so need it to be a formula or VB line.


    Any help is much appreciated. Thank you!

  • Re: Pulling multiple Values from a list


    Hi Brister,


    If you have 3 separate ranges, what links a salesman to his accounts, or are all 3 of the ranges parts of the same table?


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Pulling multiple Values from a list


    Thanks for your response Batman,


    I have them in the same sheet. A is Range 1, B is Range2, and C is Range 3. I separated them into their own range for other functions, but I am open if I need to combine into 1 Range for this function to work. Basically, the drop down box will list Range1, and then when I choose a Salesman, It will List all the accounts for that salesman. I am open to populating cells, or even a list box.


    Re-reading, I may have not answered the question, but the ranges are in orer from right to left, so A2 is the Salesman, B2 is that salesman's account, and C2 is the area of that account. But of course the same salesman can have multiple accounts.

  • Re: Pulling multiple Values from a list


    Batman, looks awesome, but can you do that so the filter puts the list in cells, so as an example:


    Fred is chosen, then it will put the list in cells so for example it will look like:
    A B
    Fred:
    2 ACCT1
    3 ACCT4
    4 ACCT7


    I hope this makes sense.


    Thanks again for your help!!

  • Re: Pulling multiple Values from a list


    Brister,


    I have amended the program so that it writes the results of the selection to row I of the worksheet. I assume you are aware that you can get the same results by applying a filter to the original table (albeit not in a separate area of the worksheet).


    Regards,
    Batman.

  • Re: Pulling multiple Values from a list


    Batman, that is right on. Thank you!!
    I could use filters, but I am pulling the ranges from different worksheets in different formats.


    I very much appreciate your help!!

  • Re: Pulling multiple Values from a list


    Just one more question Batman, instead of using a drop down box to choose the salesman, I am using a list box(Listbox1), and when I click on the name it does the same action of listing the accounts in a cell. But I tried modifying your code for that, but it just gives me errors. Any suggestions?

  • Re: Pulling multiple Values from a list


    Without seeing your code it's difficult to say, but instead of my previous code


    try substituting

    Code
    With Worksheets("Sheet1")
            .Columns("I").Clear
            lngRows = .Range("A65536").End(xlUp).Row
            .Range("A1:C" & lngRows).AutoFilter Field:=1, Criteria1:=Me.ComboBox1.Value
            Set rngAccounts = .Range("B2:B" & lngRows).SpecialCells(xlCellTypeVisible)
            .Range("I1") = "SALESMAN: " & Me.TextBox1.Value
            rngAccounts.Copy .Range("I2")
            .AutoFilterMode = False
        End With


    You should also delete the code in the UserForm_Initialize procedure, which populates the combobox with a list of the salesmen as the form is being opened.


    Hope this helps.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Pulling multiple Values from a list


    Yah I tried similar to what you had, I just changed ir to Listbox1.value, but I am getting a "Runtime Error 1004" "Clear Method of Range class failed" And it is highlighting the line:

    Code
    .column("I").clear



    Here is the code:



    Thanks again for all your hard work on this Batman.

  • Re: Pulling multiple Values from a list


    Brister,


    Without seeing your actual workbook I can't think why .Clear should fail, unless you have actually typed "Column" instead of "Columns". Is there anything unusual about column I of Sheet1, e.g. protected?


    I have changed the combobox to a listbox and the attached works for me.


    Regards,
    Batman.

  • Re: Pulling multiple Values from a list


    Hey Batman,


    I figured out why I am getting that error. I was setting the Listbox with a ListFillRange property set since I knew the Salesman was not changing. But If I had that range in any cels that were affected by the filter, it would effect the range, and give me that error. So I have to make sure I put that range where it will not be changed by the filter, or the column cleared area and that seems to have fixed it. I attached it so you could see what I was talking about. SO my question is, is there a way to keep the range from being affected and causing the errors or is the way I did it the only way to keep it working (other than doing the additem into the list box on workbook activation)?

  • Re: Pulling multiple Values from a list


    Brister,


    The line of code

    Code
    lngRows = .Range("A65536").End(xlUp).Row


    is defining the last row in the worksheet to include in the filter by doing effectively a Ctrl + Up arrow from cell A65536. If you have you list in column A this will be included in the filter as the code stands. You could change the code to

    Code
    lngRows = .Range("A1").End(xlDown).Row


    and this would exclude the list. However, if you have any blank rows in your data, anything below the first blank row will be ignored. You could equally well measure upwards from the first cell in your list.


    From a data structure point of view, having your list below your data is not a good idea. It would be better to have it in a separate worksheet altogether if possible, so that you allow for new items to be added to the list of accounts.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!