Application.VLookup returns ZERO instead of a number

[xenon99]

Hi,

I have spent too many hours on this simple macro which just doesn't seem to work ultimately.

I am trying to bring data from one excel sheet into another using VLookup. The two sheets have different time frequencies in that one is weekly and the other daily. My code returns NA's instead of integers.

The problem is with the Application.VLookup. Here is my code and I would be grateful for any help!

Please Login or Register  to view this content.

Note: msgbox(last_fri) already returns the correct value; a date. The value I am aiming to retrieve is in the 8th column of the sheet"Weekly".

Thanks,
Darius

[Bob Phillips]

If you lookup table is a single column, H5:H1500, you can possibly compare the lookup value against the 8th column in the lookup table, it just doesn't have a 2nd never mind an 8th.

[xenon99]

Hi Bob, can you clarify what you mean? Thanks, Darius

[Paul]

Hi Darius,

I don't think the Range argument you're using in the Vlookup is correct. Perhaps try:

Please Login or Register  to view this content.

I think Bob saw the $a$5:$h$1500 and thought it was $h$5:$h$1500.

[JosephP]

I reckon that if there is an exact match on the date (which is what you are looking for) then you oughta be using:

Please Login or Register  to view this content.

I'd use the range syntax Paul mentioned myself but either one should work fine.

[Bob Phillips]

I think Bob saw the $a$5:$h$1500 and thought it was $h$5:$h$1500.

I saw Paul, I did . My age is destroying my eyes.

[Paul]

With any luck, Bob, you'll be a rabbit in your next life, eating lots of carrots and having perfect vision. And lots of little rabbits.

[xenon99]

Hi guys, many thanks for the prompt replies. Josephs Cdbl function ultimately did the trick!! I can go to sleep now!!! Have a good one everyone.