Listbox value as Vlookup to combobox value

Question

Worksheet: MASTER DATA

Column A = Engine.
Column B = Part Number.
Column C = Status.
Column D = Name.
Column E = Quantity.
Column F = Date.
Column G = Priority.
Column H = Buyer.

Situation: I enter all the data from column A to H manually. I made a userform to enter data from column J to M.

In my Userform:

If I select the buyer in the combobox, I want the listbox to populate with all the partnumbers in sheet "MASTER DATA" that correspond to that specific buyer. I was thinking about using a vlookup to the buyer value, and then maybe offsetting -6 to pick up the partnumber value? Any help is appreciated.

My Code so far:

Private Sub UserForm_Initialize()
    With cboBuyer
        .AddItem "DANIEL"
    End With
End Sub

Private Sub cboBuyer_Change()
    Me.lbPartNumber.Clear
    Select Case Me.cboBuyer.Value
        Case "DANIEL"
            With Me.lbPartNumber
                .AddItem "(VLOOKUP VALUES TO THE BUYER NAME (DANIEL)
            End With
    End Select
End Sub

Answer 1

solution

Private Sub cboBuyer_Change()

Dim i

Me.lbPartNumber.Clear

Dim lastrow As Long
lastrow = Sheets("MASTER DATA").Cells(Rows.count, "B").End(xlUp).Row

Select Case Me.cboBuyer.Value
    Case "DANIEL"
        With Me.lbPartNumber
            For i = 1 To lastrow   ' Rowcount
                Sheets("MASTER DATA").Activate
            If Cells(i, 8) = "DANIEL" Then
                .AddItem Cells(i, 2)
            End If
            Next i
        End With
    End Select