vlookup problem

[Alessandro]

Hello,
I am editing a macro in VB under Excel 2002.
I have created this function

verticale = Application.WorksheetFunction.VLookup(Range(cella).Text,
Sheets(foglio).Range(area), colonna, False)

When I call it in my macro, it's so made.
- cella is "A" & a progressive number (for...next)
- foglio is a worksheet on the same file
- area is a defined range in foglio (a named range)
- colonna is a simple number, of course

What I need to do is to say: if you don't find 'cella' in foglio!area look
for it in another foglio!area (where you of course will find it).
When the value in 'cella' is found in the first foglio!area it functions
correctly.
But if the value is not found, I get an error:

Run-time error '1004'
Vlookup property for the class WorksheetFunction not found (or similar, I
get the message in italian).

How could I solve the problem?
Thank you.

Alessandro

 

[Niek Otten]

Hi Alessandro,

Shouldn't you use Range("area")? Or is area a variable that contains the name of the range?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hello,
| I am editing a macro in VB under Excel 2002.
| I have created this function
|
| verticale = Application.WorksheetFunction.VLookup(Range(cella).Text,
| Sheets(foglio).Range(area), colonna, False)
|
| When I call it in my macro, it's so made.
| - cella is "A" & a progressive number (for...next)
| - foglio is a worksheet on the same file
| - area is a defined range in foglio (a named range)
| - colonna is a simple number, of course
|
| What I need to do is to say: if you don't find 'cella' in foglio!area look
| for it in another foglio!area (where you of course will find it).
| When the value in 'cella' is found in the first foglio!area it functions
| correctly.
| But if the value is not found, I get an error:
|
| Run-time error '1004'
| Vlookup property for the class WorksheetFunction not found (or similar, I
| get the message in italian).
|
| How could I solve the problem?
| Thank you.
|
| Alessandro

 

[Stefi]

It's normal in Not found case. You have to use an On Error structure to
separate Found and Not found cases, something like this:

verticale =0 'or "" depending on data type of colonna
On error Resume Next

verticale = Application.WorksheetFunction.VLookup(Range(cella).Text,
Sheets(foglio).Range(area), colonna, False)

if verticale = 0 then
' Not found cases
Else
' Found cases
End if

Regards,
Stefi


„Alessandro†ezt írta:

 

[Alessandro]

I thought that there was a parameter in vlookup in this cases.
However, I have solved my problem in the way that you suggest.
Thank you.

 

[Alessandro]

I say area (and not "area") because area is a parameter of my function.
However in the resulting command it appears like "area".

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„Alessandro†ezt írta:

 

[Alessandro]

You simply deserved it
I'm sorry to see that many threads here around miss a rating.

 

[Stefi]

Perhaps newsgroup software should generate an extra line at the end of posts:

"PLEASE RATE THIS POST!"

Stefi


„Alessandro†ezt írta:

 

[Bob I]

Folks who use real newsreaders ignore that and wouldn't see it anyway.

 

[Stefi]

Bob, what is a real newsreader, how to use it and why is it better than this
forum?
Stefi


„Bob I†ezt írta:

 

[Bob I]

A program for downloading reading and replying to messages on usenet
(which is where all this info really is). The "forum" you refer to is
merely a "web viewer" of the contents and is "problematic" software to
say the least. Outlook Express is one reader that comes with Windows but
you are free to use whatever one you choose and there are several.

http://aumha.org/nntp.htm
and
http://www.newsreaders.com/guide/news.html