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

It should work.

is the code called from the form?

and in what way does it not work?

--
__________________________________
HTH

Bob

wrote in message
...
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

On Aug 7, 3:40*pm, " 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



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

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

On Aug 7, 7:46*pm, Dave Peterson wrote:
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

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