VBA Macro for Userform

[Zanaba]

I have an excel workbook that I have made a userform for. My plan for this form is to have the user pick a location from the first combo box and pick another location from the second combo box. After picking these two locations the user will click the run button to run the macro.

Example of Sheet1 data going down will grow, but width of table will not go past column F.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Miami, FL[/TD]
[TD]Oakland, CA[/TD]
[TD]New York, NY[/TD]
[TD]Chicago, IL[/TD]
[/TR]
[TR]
[TD]TMS[/TD]
[TD]Nashville, TN[/TD]
[TD]$100[/TD]
[TD]$200[/TD]
[TD]$300[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]ATS[/TD]
[TD]Memphis, TN[/TD]
[TD]$200[/TD]
[TD]$300[/TD]
[TD]$100[/TD]
[TD]$150[/TD]
[/TR]
[TR]
[TD]NT[/TD]
[TD]Fresno, CA[/TD]
[TD]$70[/TD]
[TD]$120[/TD]
[TD]$152[/TD]
[TD]$170[/TD]
[/TR]
[TR]
[TD]NT[/TD]
[TD]Memphis, TN[/TD]
[TD]$50[/TD]
[TD]$70[/TD]
[TD]$100[/TD]
[TD]$150[/TD]
[/TR]
[TR]
[TD]ATS[/TD]
[TD]Nashville, TN[/TD]
[TD]$100[/TD]
[TD]$150[/TD]
[TD]$80[/TD]
[TD]$90[/TD]
[/TR]
[TR]
[TD]TMS[/TD]
[TD]Fresno, CA[/TD]
[TD]$200[/TD]
[TD]$70[/TD]
[TD]$300[/TD]
[TD]$250[/TD]
[/TR]
</tbody>[/TABLE]


So the first combo box, the user will chose either Miami, FL Oakland, CA New York, NY or Chicago IL

Second combo box, the user will choose from the other locations(Nashville, Memphis, Fresno)

So user chooses Miami, FL and Fresno, CA and clicks start.

The macro will then search Sheet1 and find all the results for Miami, FL and Fresno, CA and will make a new sheet and return the results for all matching the two locations.

Result Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]COLUMN A[/TD]
[TD]COLUMN B[/TD]
[TD]COLUMN C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Miami, FL[/TD]
[/TR]
[TR]
[TD]NT[/TD]
[TD]Fresno, CA[/TD]
[TD]$70[/TD]
[/TR]
[TR]
[TD]TMS[/TD]
[TD]Fresno, CA[/TD]
[TD]$200[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

What code do you have so far?

 

[Zanaba]

I have the code that can move things around. Just struggling getting it to search based off the options picked in the combolists and returning the results in a new sheet. I am not sure how to get the macro to, not sure of the right word, but read what is in the combo lists and search based off of that.

 

[Fluff]

Could you post your existing code?

 

[Fluff]

Try

[COLOR=#0000ff]Option Explicit
Private Dic As Object[/COLOR]

Private Sub StartBtn_Click()
   Dim Orow As Variant
   Dim Drow As Long
   
   Drow = 2
   Sheets.Add
   Range("C1").Value = ComboBox1.Value
   For Each Orow In Split(Dic(ComboBox2.Value), ",")
      With Sheets("Sheet1")
         .Range("A" & Orow).Resize(, 2).Copy ActiveSheet.Range("A" & Drow)
         .Cells(Orow, ComboBox1.ListIndex + 3).Copy ActiveSheet.Range("C" & Drow)
         Drow = Drow + 1
      End With
   Next Orow
End Sub

Private Sub UserForm_Initialize()

   Dim Cl As Range

   Set Dic = CreateObject("scripting.dictionary")

   With Sheets("Sheet1")
      If .AutoFilterMode Then .AutoFilterMode = False
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         If Not Dic.exists(Cl.Value) Then
            Dic.Add Cl.Value, Cl.Row
         Else
            Dic(Cl.Value) = Dic(Cl.Value) & "," & Cl.Row
         End If
      Next Cl
   End With
   ComboBox2.list = Application.Transpose(Dic.keys)
   ComboBox1.list = Application.Transpose(Range("C1:F1"))
End Sub

The 2 lines in blue MUST go at the very top of the module (before any code).
Change the 2 combobox names & the Start button name if needed.