vlookup from a user form?

[Mikal]

I have a user form that I would like to modify the code on, It has a list box that pulls up a part#, then it has a text box for the cost of that part. In the text box I would like to do a vlookup that refers to the list box and pulls in the cost from a table. Currently the text box has this code.

Private Sub In_CostPerUnit_Change()
'Fill with PN In_CostPerUnit
In_CostPerUnit.Value = Range("U12").Value

is there code I can add to do a vlooup?

thanks in advance for your help

 

[DanteAmor]

Delete your code and try with the following.

I guess the parts# are in column T on sheet 1 and the costs in column U. Change the data in red for your data.

Private Sub ListBox1_Click()
  Dim f As Range
  Set f = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("[COLOR=#ff0000]T:T[/COLOR]").Find(ListBox1.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    In_CostPerUnit.Value = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Cells(f.Row, "[COLOR=#ff0000]U[/COLOR]").Value
  End If
End Sub

It works like this, every time you select an item from the listbox, it automatically searches and fills the textbox.

 

[Mikal]

Hi Dante,

Works great! Thank you for the code.

 

[DanteAmor]

Hi Dante,

Works great! Thank you for the code.

I'm glad to help you. Thanks for the feedback.