Range in function

[mjpearson]

I've hit the wall. I think I've tried every possible combination and still can't get it to work. I've broken it down to the very simplest of tests and still can't get it to work. Can someone guide me to a solution?

I'm having problems with passing the RANGE to VLOOKUP. My spreadsheet has a range named "peak_hour_table" and in the table, the first column is comprised of dates. The second and third columns are times (decimals).

Function test_function(event_month As Date) As Date

test_function = _
    Application.WorksheetFunction.VLookup(CDate(event_month), _
    Worksheets("Seasons").Range("peak_hour_table"), _
    2)
    
End Function

Thanks, mike

 

[GlennUK]

Change your CDate to CLng, and it will work ( you must use the Long version of dates when doing VLOOKUP or MATCH on dates in VBA ).

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Bong]

Also, I guess it might not matter but named ranges include the worksheet reference.

_________________
Bob Rashkin

 

[mjpearson]

Oh no! It can't be that easy. I spent way to much time trying to figure it out only to be this simple.

Thanks Glenn,

Much appreciated.

 

[GlennUK]

My pleasure!

Cheers, Glenn.

Beauty is in the eye of the beerholder.