Help with VBA Vlookup
 

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

Help with VBA Vlookup
 

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.

Help with VBA Vlookup
 

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