I'd try something like:

Dim res as variant
....
res = Application.VLookup(me.TextBox1.value, Sheet4.Range("staff"), 3, False)
if iserror(res) then
me.textbox6.value = "No Match!"
else
me.textbox6.value = res
end if

And if the values in the first column of that Staff range are numbers, you'll
want to convert me.textbox1.value to a number

if isnumeric(me.textbox1.value) then
res = Application.VLookup(clng(me.TextBox1.value), _
Sheet4.Range("staff"), 3, False)
else
res = "Invalid entry in textbox1"
end if






" wrote:

Hi All,
i really wonder why I cannot refer the textbox value in the userform
while using vlookup function in VBA.
For example:
' TextBox6 = Application.VLookup(Userform1.TextBox1.value,
Sheet4.Range("staff"), 3, False)
Any idea why above code does not work?

Thanks a lot
Baha

--

Dave Peterson