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