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

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

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

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:

.column("I").clear

Here is the code:

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


End Sub

Thanks again for all your hard work on this 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)?