Vba Code Combobox Lookup and Return all Value not jus the First value.

[TonySondergeld]

I Have 2 Comboboxs, the first Combobox 1 choose the Product from a list of products, when the product is selected. it lookup a range (for example from sheet 2 H4:G1500) it then match all products that match the First combobox value and return all value from the second column of the range and place it into the Combobox 2
Code so far, will place number in Combobox 2 but only the first entry.
Dim myRange As Range
Sheets("Sheet").Combobox1.Clear
Set myRange = Sheets("Sheet2").Range("G4:H1500")
i = Combobox1.Value
Combobox2.Value = Application.VLookup(i, myRange, 2, False)

 

[royUK]

What kind of ComboBoxes are you using?

 

[TonySondergeld]

Active x Controls

 

[royUK]

VLOOKUP is not suitable for this. If I understand what you mean then probably the best way would be to use AdvancedFilter and create a temporary list of matches which can be deleted after the combobox is loaded.

Kave you actually got 1500 rows of data or is that a random number/

 

[TonySondergeld]

It is a stock list of production for byproduct, so every bag that is produced is given a number that is random sequence, So I would Like to select the Product with the first combobox1 and then have the sequence numbers load into the combobox2 so when the correct number is select it will finish filling out the Form details for the product and print off the product flag. That last part work great when pulling number in manually.

 

[TonySondergeld]

Thanks for your help I worked it out with the Following Code a Advance Filter Worked.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Filter").Range("B2").Value = CStr(CBX_ProductList.Value) 'Linked Combobox1 to Cell Filter!B2
Sheets("Print Bag Flags").CBX_BagNo.Value = "Select a Bag Number" 'Tells operator to select the Bag number
Sheets("Bag Info Data").Activate 'Make sheet activate
Sheets("Bag Info Data").Range("G3,H3").Select 'then select this range
Selection.Copy ' Copy's the range
Sheets("Filter").Select ' Select the Worksheet Filter
Sheets("Filter").Range("B4:C4").PasteSpecial ' Paste the Copied range above to this range on filter worksheet
Sheets("Filter").Columns("B:B").EntireColumn.AutoFit ' Adjust the column to fit the data pasted
Sheets("Filter").Columns("C:C").EntireColumn.AutoFit ' Adjust the column to fit the data pasted
Sheets("Filter").Range("D4").Select ' Select a random cell
Sheets("Bag Info Data").Select ' Select the Worksheet Bag info Data
Application.CutCopyMode = False ' turn off the select copy cells
Sheets("Bag Info Data").Range("G4").Select 'Select a Random Cell
Sheets("Filter").Select ' Select the Worksheet Filter
' Preforms the Advance Filter
Sheets("Bag Info Data").Range("G3:H1500").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Filter").Range("B1:B2"), CopyToRange:=Sheets("Filter").Range("B4:C4"), Unique:=False
Sheets("Print Bag Flags").Activate ' Select this worksheet
Application.ScreenUpdating = True
Application.DisplayAlerts = True

 

[royUK]

Well done.

However, you don't need to Activate sheets and Ranges, especially random cells. I can't test this but seeif it works

Option Explicit


Sub x()
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False


        Sheets("Filter").Range("B2").Value = CStr(CBX_ProductList.Value)    'Linked Combobox1 to Cell Filter!B2
        Sheets("Print Bag Flags").CBX_BagNo.Value = "Select a Bag Number"    'Tells operator to select the Bag number
        Sheets("Filter").Select    ' Select the Worksheet Filter
        Sheets("Filter").Range("B4:C4").Value = Sheets("Bag Info Data").Range("G3,H3").Value
        Sheets("Filter").Columns("B:C").EntireColumn.AutoFit    ' Adjust the column to fit the data pasted


        .CutCopyMode = False    ' turn off the select copy cells


        ' Preforms the Advance Filter
        Sheets("Bag Info Data").Range("G3").CurrentRegion.AdvancedFilter _
            Action:=xlFilterCopy, CriteriaRange:=Sheets("Filter").Range("B1:B2"), _
            CopyToRange:=Sheets("Filter").Range("B4:C4"), Unique:=False


        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub