I bet there isn't a match for your vlookup.

When I work with worksheet functions inside VBE, I (almost) always drop the
..worksheetfunction portion.

There's a few functions where application.worksheetfunction.xxx and
application.xxx handle things differently.

Two of them are .match and .vlookup.

Used like application.worksheetfunction.vlookup(), if no match is found, then an
error is raised:

dim Res as variant
on error resume next
res = application.worksheetfunction.vlookup(...)
if err.num < 0 then
'no match found
err.clear
end if
on error goto 0


But if you use it like: application.vlookup(), an error can be returned:

dim res as variant
res = application.vlookup(...)
if iserror(res) then
'no match found
end if

I find the second version easier.

=============

So in your case:

dim res as variant
res = Application.WorksheetFunction.Vlookup(ComboBox1.Va lue, _
Sheets("Out").Range("B:M"), 6, False)

if iserror(res) then
txcon.value = "No match!"
else
txcon.value = res
end if

(untested, so watch for typos!)

Bimal wrote:

Hi guys,
I was using vlookup in a combobox.click event to populate the text box
as per the value of combobox. I came across a rather strange behaviour
of VBA.
The problem line is
Txcon.Value = Application.WorksheetFunction.Vlookup(ComboBox1.Va lue,
Sheets("Out").Range("B:M"), 6, False)

Txcon is textbox. This gives run-time error 1004 "Unable to get the
vlookup property of the worksheetfunction class".

However the same line works fine if I change the sheet name from "Out"
to "In"

I cant belive there is my mistake coz it works properly if change the
sheet name.
Is this a bug or something else?

Hope some expert can answer me.
Bimal.

--

Dave Peterson

Dave,
Its not true. When I came across this error, I make sure to use only
those value where there is a match. Moreover, if there is no match,
the value will not appear in the combobox.
Its nice to learn about the difference. Now I have tried with both
but without success.
While using only application.vlookup, I got run-time error
'-2147352571(80020005)' Could not set the value property, Type
mismatch.
This is new error. Does it mean something? If I use the vlookup in one
of the cell, instead of VBA, for the same value, it works.
Regards,
Bimal

Dave Peterson wrote in message ...
I bet there isn't a match for your vlookup.

When I work with worksheet functions inside VBE, I (almost) always drop the
.worksheetfunction portion.

There's a few functions where application.worksheetfunction.xxx and
application.xxx handle things differently.

Two of them are .match and .vlookup.

Used like application.worksheetfunction.vlookup(), if no match is found, then an
error is raised:

dim Res as variant
on error resume next
res = application.worksheetfunction.vlookup(...)
if err.num < 0 then
'no match found
err.clear
end if
on error goto 0


But if you use it like: application.vlookup(), an error can be returned:

dim res as variant
res = application.vlookup(...)
if iserror(res) then
'no match found
end if

I find the second version easier.

=============

So in your case:

dim res as variant
res = Application.WorksheetFunction.Vlookup(ComboBox1.Va lue, _
Sheets("Out").Range("B:M"), 6, False)

if iserror(res) then
txcon.value = "No match!"
else
txcon.value = res
end if

(untested, so watch for typos!)

Bimal wrote:

Hi guys,
I was using vlookup in a combobox.click event to populate the text box
as per the value of combobox. I came across a rather strange behaviour
of VBA.
The problem line is
Txcon.Value = Application.WorksheetFunction.Vlookup(ComboBox1.Va lue,
Sheets("Out").Range("B:M"), 6, False)

Txcon is textbox. This gives run-time error 1004 "Unable to get the
vlookup property of the worksheetfunction class".

However the same line works fine if I change the sheet name from "Out"
to "In"

I cant belive there is my mistake coz it works properly if change the
sheet name.
Is this a bug or something else?

Hope some expert can answer me.
Bimal.

What's in your combobox? Is it numeric? If yes, then you may want to convert
it to a number before you do the =vlookup() stuff.

text numbers < number numbers
'123 < 123

======
And I don't think that funny error message is the result of using
application.vlookup() instead of application.worksheetfunction.vlookup() <<but
I've been wrong before.

I searched google for your error number in the *excel* newsgroups and got a
couple of hits.

http://groups.google.com/groups?as_q...ugroup=*excel*

Anything different happen if you declare a variable as a variant and then plop
the vlookup() value into it.



Bimal wrote:

Dave,
Its not true. When I came across this error, I make sure to use only
those value where there is a match. Moreover, if there is no match,
the value will not appear in the combobox.
Its nice to learn about the difference. Now I have tried with both
but without success.
While using only application.vlookup, I got run-time error
'-2147352571(80020005)' Could not set the value property, Type
mismatch.
This is new error. Does it mean something? If I use the vlookup in one
of the cell, instead of VBA, for the same value, it works.
Regards,
Bimal

Dave Peterson wrote in message ...
I bet there isn't a match for your vlookup.

When I work with worksheet functions inside VBE, I (almost) always drop the
.worksheetfunction portion.

There's a few functions where application.worksheetfunction.xxx and
application.xxx handle things differently.

Two of them are .match and .vlookup.

Used like application.worksheetfunction.vlookup(), if no match is found, then an
error is raised:

dim Res as variant
on error resume next
res = application.worksheetfunction.vlookup(...)
if err.num < 0 then
'no match found
err.clear
end if
on error goto 0


But if you use it like: application.vlookup(), an error can be returned:

dim res as variant
res = application.vlookup(...)
if iserror(res) then
'no match found
end if

I find the second version easier.

=============

So in your case:

dim res as variant
res = Application.WorksheetFunction.Vlookup(ComboBox1.Va lue, _
Sheets("Out").Range("B:M"), 6, False)

if iserror(res) then
txcon.value = "No match!"
else
txcon.value = res
end if

(untested, so watch for typos!)

Bimal wrote:

Hi guys,
I was using vlookup in a combobox.click event to populate the text box
as per the value of combobox. I came across a rather strange behaviour
of VBA.
The problem line is
Txcon.Value = Application.WorksheetFunction.Vlookup(ComboBox1.Va lue,
Sheets("Out").Range("B:M"), 6, False)

Txcon is textbox. This gives run-time error 1004 "Unable to get the
vlookup property of the worksheetfunction class".

However the same line works fine if I change the sheet name from "Out"
to "In"

I cant belive there is my mistake coz it works properly if change the
sheet name.
Is this a bug or something else?

Hope some expert can answer me.
Bimal.

--

Dave Peterson