Use VLOOKUP to populate text box on form

[Greg Snidow]

Greetings everyone. I'm not sure if I can do this, but on a user form I have
a combo box that populates from a range on sheet 'DATA', with items. There
is also a text box that I want to find the price of that item. Thoretically,
assuming my data is such that item is in column A of 'DATA', starting at row
18, and price is in column B of 'DATA', how can I get the price of the item
selected from the combo box. I found a post by Dave Peterson to populate the
list of the combo, but I am at a loss as to how to populate the text box for
price. Any ideas? Below is the code to populate the list(thanks Dave).

Private Sub UserForm_Initialize()
With Worksheets("Data")
Me.cboFuel1.List = .Range("A18", .Cells(.Rows.Count,
"A").End(xlUp)).Value
End With

Greg

 

[Joel]

Try this

Private Sub UserForm_Initialize()
With Worksheets("Data")
Me.cboFuel1.List = .Range("A18", _
.Cells(.Rows.Count, "A").End(xlUp)).Value
End With
End Sub

Private Sub cboFuel1_Change()
With Worksheets("Data")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set DataRange = .Range("A18", "A" & Lastrow)
SelectedItem = Me.cboFuel1.Value
Set c = DataRange.Find(what:=SelectedItem, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
TextBoxdata = c.Offset(0, 1)
End If

End With
End Sub

 

[Jacob Skaria]

Try the below which is the combo change event...which will lookup the
corresponding value in ColB and populate that to me.textbox1

Private Sub cboFuel1_Change()
Me.TextBox1 = WorksheetFunction.VLookup(Me.ComboBox1, _
Worksheets("Data").Range("A:B"), 2, 0)
End Sub


If this post helps click Yes

 

[FSt1]

hi
add something like this to your combo box change event
Private Sub Cbo1_Change()
Dim cbv As String
Dim r As Range
cbv = Me.Cbo1.Value
Set r = Range("A18:B24")
Me.TextBox1.Value = WorksheetFunction.VLookup(cbv, r, 2)

regards
FSt1
End Sub

 

[Jacob Skaria]

Typo...
comboname is cboFuel1
textbox name is TextBox1

Private Sub cboFuel1_Change()
Me.TextBox1 = WorksheetFunction.VLookup(Me.cboFuel1, _
Worksheets("Data").Range("A:B"), 2, 0)
End Sub

If this post helps click Yes

 

[Patrick Molloy]

make the combox columncount = 2 and set the BoundColumn =2

set the source to both columns A and B of your data


if you don't want to see the value of B in the combobox, then set its column
width to zero

now, when you click an item (change) the value returned will be the value
thats in the 2nd column

 

[Dave Peterson]

You can create a combobox that has multiple columns (and even hide the second
column from view) and then just get the second column's value from the
combobox--never go back to the worksheet.

Option Explicit
Private Sub ComboBox1_Change()
With Me.ComboBox1
If .ListIndex < 0 Then
'nothing chosen, clear the textbox
Me.TextBox1.Value = ""
Else
Me.TextBox1.Value = .List(.ListIndex, 1)
End If
End With
End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("Data")
Set myRng = .Range("A18:B" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With Me.ComboBox1
.ColumnCount = myRng.Columns.Count
.List = myRng.Value
.ColumnWidths = "100;0" 'hide the second column
End With
End Sub

 

[Greg Snidow]

Wow, thank you all so much for the good solutions, all of them work very
well. Coincidentally, I messed around with it last night, and got it to work
in a much less elegant fashion. Below is the code, which will promptly be
deleted. One question I have about my version, is that I could not set the
LstRow variable by any means I found here. I know it is a simple syntax
error.

Dim StartCellAddress As String
Dim CurrentCellString As String
Dim LookupValue As Double
StartCellAddress = Sheets("data").Range("A2").Address
CurrentCellString = Sheets("data").Range(StartCellAddress).Value

Dim LstRow As Integer
'LstRow = Sheets("data").Cells(.Rows.Count, "A").End(xlUp).Row
LstRow = 13
Dim MyCell As String
Dim Rng As Range
Set Rng = Sheets("data").Range("A2:A" & LstRow)

LookupValue = Sheets("data").Range(StartCellAddress).Offset(0, 1).Value
For n = 1 To LstRow
If Me.cboFuel1.Value = CurrentCellString Then
Me.txtPrc1.Value = LookupValue
Exit For
Else
StartCellAddress = Sheets("data").Range("A2").Offset(n, 0).Address
CurrentCellString = Sheets("data").Range(StartCellAddress).Value
LookupValue = Sheets("data").Range(StartCellAddress).Offset(0,
1).Value
End If
Next

 

[Greg Snidow]

Jacob, I hit the "yes" button, for whether or not this post answered the
question, but for some reason yours is not showing up. I just wanted to let
you know I did not omit your solution. Maybe its a bug?