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

TonySondergeld

New Member
Joined
Jul 11, 2009
Messages
31
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)
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
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

New Member
Joined
Jul 11, 2009
Messages
31

ADVERTISEMENT

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

New Member
Joined
Jul 11, 2009
Messages
31
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-known Member
Joined
Jul 24, 2002
Messages
1,606
Well done.

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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,631
Messages
5,523,992
Members
409,554
Latest member
denistrevisan

This Week's Hot Topics

Top