Help with VBA Vlookup

[Bimal]

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.Value,
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.

 

[Frank Kabel]

Hi
just a guess: The worksheet 'Out' exists in your current workbook (no
spaces or anything else in this name).

Does the line
=Sheets("Out").Range("A1")
in the Immediate window work?

 

[Bimal]

Hi Frank,
I didn't understand what it means. I have typed the same
=Sheets("Out").Range("A1")in the immediate window but dont know what
to do next and what for this window is or how this window is utilised
may be more appropriate.
But thanks for reply.
Bimal.

 

[Frank Kabel]

Hi
does this line in the Immediate window return an error or the value
from your sheet 'out'. this line was just for testing purpose if sheet
'out' does exist in your workbook

 

[Bimal]

Frank,
Sheet "Out" does exists. I run my macro without this line and that
runs fine in sheet Out.. I am sorry but I really do not know how to
check in immediate window. Just typying this line dont show error. Or
typing anything dont show error in immediate window.
Morover, this error occurs in another sheet Rut also. It only works in
the Sheet In.

Any thoughts?
Bimal.

 

[Dave Peterson]

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.Value, _
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]

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]

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=2147352571&as_ugroup=*excel*

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