Vlookup funtion in Userform

[baha17]

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

 

[Bob Phillips]

It should work.

is the code called from the form?

and in what way does it not work?

 

[baha17]

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

Hi,
Yes it is called from the userform by clicking command button in the
userform1 ad it gives run time error like"could not set the value
property.Type mismatch"
Thanks for the help
Baha

 

[Dave Peterson]

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

 

[baha17]

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

Thanks a lot Dave it works fine. I did not know that "CLng" thing
have a good day to all
Baha