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

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


"Alessandro" wrote in message ...
| 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

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

"Niek Otten" wrote:

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


"Alessandro" wrote in message ...
| 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

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:

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

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.

"Stefi" wrote:

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:

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

You are welcome! Thanks for the feedback!
Stefi

€žAlessandro€� ezt Ã*rta:

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.

"Stefi" wrote:

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:

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

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

"Stefi" wrote:

You are welcome! Thanks for the feedback!
Stefi

€žAlessandro€� ezt Ã*rta:

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.

"Stefi" wrote:

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:

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

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

"PLEASE RATE THIS POST!"

Stefi


€žAlessandro€� ezt Ã*rta:

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

"Stefi" wrote:

You are welcome! Thanks for the feedback!
Stefi

€žAlessandro€� ezt Ã*rta:

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.

"Stefi" wrote:

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:

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