Vlookup VBA on dates

[gianni gianni]

Hello,

I'm writing a piece of VBA code and I need to run a vlookup.

In particular, I've written the following code

fir = Application.WorksheetFunction.VLookup(period, Range("curve"), 2)

where:

- fir is a number;
- period: is a date written by a named range;
- curve: is a named range with dates on the 1st column and numbers in
the other columns.

I need to select a number (fir) for the nearest date to "period" in the
"curve" matrix.

When I run the model, it gets me the error:

Runtime error '1004' - "Unable to
get the Vlookup property of the WorksheetFunction class".

Why?

*** Sent via Developersdex http://www.developersdex.com ***

 

[Pranav Vaidya]

this error means Vlookup() failed.
to handle this error add an error handling routine such as

On error goto <lable>

also change the datatype of the variable 'fir' to variant

 

[Bob Phillips]

The question is, why did it fail?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)