vlookup problem

A

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
 
N

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
 
S

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)
Click to expand...
if verticale = 0 then
' Not found cases
Else
' Found cases
End if

Regards,
Stefi


„Alessandro†ezt írta:
 
A

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.
 
A

Alessandro

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

Alessandro

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

Stefi

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

"PLEASE RATE THIS POST!"

Stefi


„Alessandro†ezt írta:
 
S

Stefi

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


„Bob I†ezt írta:
 
Top