VBLookup on Combobox1 - ERROR

[Bryan123]

Hi,

In need of your help. Cant seem to move forward on this problem. I have a combobox with a list of emp ID's, when I choose one from the list, it should populate an info to the textbox(workstream) but to no avail. Alwas goes to the error "not found". Please see attached

Private Sub Combobox1_Change()
Dim rng as range
set rng = worksheets("LRS sampling").range("A2:L200")
On Error Resume Next
workstream.value = application.worksheetfunction.vlookup(combobox1,Rng,3,False)
If Err.Number <> 0 Then workstream.value = "not found"
THanks

Bryan

 

[Fluff]

Hi & welcome to MrExcel.
What are your emp ID's?

 

[RoryA]

Welcome to the forum.

Are your IDs numeric? If so, you should convert to number before doing the lookup. Also if you use Application.Vlookup, you can trap an error value rather than a runtime error:

Private Sub Combobox1_Change()
Dim rng as range
set rng = worksheets("LRS sampling").range("A2:L200")
dim theValue
theValue = application.vlookup(clng(combobox1),Rng,3,False)
If iserror(thevalue) then
 workstream.value = "not found"
else
workstream.value = thevalue
end if

 

[Bryan123]

Hi.. EMP IDS should be from a column in excel

Private Sub UserForm_Initialize()

employee = Worksheets("LRS sampling").Range("A2:L200").Value
Dim ListItems As Variant, i As Integer
With Me.ComboBox1
.Clear
Application.ScreenUpdating = False
ListItems = employee
For i = 1 To UBound(ListItems, 1)
.AddItem ListItems(i, 10)
Next i
ComboBox1.ListIndex = 0
End With

End Sub

 

[Bryan123]

yes it is numeric. I have tried the code you gave but still having not found error.

 

[RoryA]

It looks like the IDs are in column J of your worksheet, but you're attempting to look them up in column A.

 

[Bryan123]

Hi Rory, yes you're right. The IDS are in column J.. Please let me know what I did wrong on the code.. Thank you

 

[Bryan123]

hi guys, please help me out.. cant seem to get out from this scenario..

 

[Fluff]

Make this change

   Set rng = Worksheets("LRS sampling").Range("[COLOR=#ff0000]J2[/COLOR]:L200")

 

[RoryA]

Which column do you want values back from - C or L? If it's L use fluff’s suggestion.