VLOOKUP or Advanced Filter in vba for my userform

[herb1]

Hi All,

I'm trying to develop a Userform to capture defects detected in an electronic manufacturing plant.
Userform (below):

I have managed to set up dependent combo boxes that helps to identify the customer and part number.
I would like the Assembly Description to be populated in a textbox of the userform.

Here is a sample of the Excel table that I'm using to populate the combo boxes and would like to populate the Assembly Description based on the Customer and Assembly No. chosen.
Bear in mind that the Assembly Descriptions are not in a single column but in various columns, based on the Customer selected and based on the row selected for the Assembly No.

For example, I would like to have the textbox for Assembly Description populated (as such):
Customer Assembly No. Assembly Description
Mr Krabs MK-006 Sunglasses

I need help here 'cos I'm lost for the vba code.
I don't know whether I should use VLOOKUP or ADVANCED FILTER.

 

[DanteAmor]

Change data in red by your information.

If you need help tell me the name of your controls.


Also put the code to use to load the combos.


Try this

Private Sub ComboCustomer_Change()
    [COLOR=#ff0000]ComboNo[/COLOR].Value = ""
    [COLOR=#ff0000]TextDes[/COLOR].Value = ""
    If [COLOR=#ff0000]ComboCustomer[/COLOR].ListIndex = -1 Then Exit Sub
End Sub


Private Sub [COLOR=#ff0000]ComboNo[/COLOR]_Change()
    [COLOR=#ff0000]TextDes[/COLOR].Value = ""
    If [COLOR=#ff0000]ComboNo[/COLOR].ListIndex = -1 Then Exit Sub
    If [COLOR=#ff0000]ComboNo[/COLOR].Value = "" Then Exit Sub
    Set b = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Rows(1).Find([COLOR=#ff0000]ComboCustomer[/COLOR], LookIn:=xlValues, lookat:=xlWhole)
    If Not b Is Nothing Then
        col = b.Column
        Set c = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Columns(col).Find([COLOR=#ff0000]ComboNo[/COLOR], LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            [COLOR=#ff0000]TextDes[/COLOR].Value = c.Offset(0, 1).Value
        End If
    End If
End Sub