using a comboxbox1 with an offset

[rholdren]

I have a piece of code that works well but I would like to take it to the next level. I have a series of vendors that are on a list and the code below works well. It searches the range locates the vendor code then the offsets copies the values onto another worksheet. what I would like to do is instead of having the same code repeated for each vendor I would like to use the list in combobox1 to select the vendor and have the offsets copy and paste the data into the other worksheet.

Sub AFCO_T()

Dim Fnd As Range

Sheets("2018Vendors").Select
Range("A2.A400").Select
Set Fnd = Columns(1).Find(What:="AFCO", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Fnd Is Nothing Then Exit Sub
Sheets("VendorTracking").Range("d6").Value = Fnd.Offset(0, 2).Value
Sheets("VendorTracking").Range("c6").Value = Fnd.Offset(0, 5).Value

End Sub

I have this same piece of code but for over 300 vendors. I would really like to just clean up the file and streamline the process. So instead of having a sub for each vendor (like AFCO_T) I could have a single sub that would do the same thing as this one but it would choose the vendor from the combobox1 list. This way if I add new vendors I won't have to add more code and if I remove a vendor I won't have to remove code or have a file bigger than it needs to me.

Any help would be greatly appreciated. Thanks

Rex

 

[Fluff]

How about

Private Sub CommandButton1_Click()
   Dim Fnd As Range

   With Sheets("2018Vendors")
      Set Fnd = .Columns(1).Find(Me.ComboBox1.Value, , xlFormulas, xlWhole, , , False, , False)
   End With
   If Fnd Is Nothing Then Exit Sub
   With Sheets("VendorTracking")
      .Range("d6").Value = Fnd.Offset(0, 2).Value
      .Range("c6").Value = Fnd.Offset(0, 5).Value
   End With
End Sub

 

[rholdren]

I don't have it connected to a command button, is that necessary? I was hoping to have it work when Combobox1 changed values

 

[Fluff]

In that case just put the code in the combobox_click event.

 

[rholdren]

Awesome. Thanks I really do appreciate it. You are the BEST!

 

[Fluff]

You're welcome & thanks for the feedback